Category: Business

Organize your DAX measures

How To Organize Your DAX Measures in Power BI Folders and Make Finding Them A Breeze

Posted on by Bb

As you progress in Power BI, you will gradually be working with more DAX measures and calculations. As a result, things can easily get cluttered. Today you will learn a few valuable tips and tricks on how to organize your DAX measures for a more efficient workflow. Hopefully this will help you and become a standard practice!

Watch the video for step by step instructions and a full explanation.

To summarize, Reid shows us how to add a new table and move our measures to it so they are together in one place.

He then shows us how to convert this into a folder, and then subsequently create subfolders to further organize your DAX measures in the model. Reid continues on to show us how we can have a measure in multiple locations if it makes sense.

What a great way to manage the measures as they grow in number!

***** Video Details *****
00:00 Introduction
00:33 Root folders
01:32 Subfolders
03:30 Actual Amount VTB

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI – http://portal.enterprisedna.co/p/ultimate-beginners-guide-to-power-bi
FREE COURSE – Ultimate Beginners Guide To DAX – http://portal.enterprisedna.co/p/ultimate-beginners-guide-to-dax
FREE – Power BI Resources – http://enterprisedna.co/power-bi-resources
FREE – 60 Page DAX Reference Guide Download – https://enterprisedna.co/dax-formula-reference-guide-download/
Enterprise DNA Membership – https://enterprisedna.co/membership
Enterprise DNA Online – http://portal.enterprisedna.co/
Enterprise DNA Events – https://enterprisedna.co/enterprise-dna-events-page/

#EnterpriseDNA #PowerBI #PowerBIDesktop #PowerBITutorial #DAX #DAXTutorial

Copy/Paste Is Not Working While Remotely Working – Solved! (Remote Desktop Issue)

Posted on by Bb

I encountered a strange issue where I suddenly could not copy or paste in Windows or any of the Office applications (Excel, Access, Word, Outlook, PowerPoint, etc.) while working remotely. It was quite frustrating as I was pressed for time and needed this critical function to keep working. I then had the idea to stop the utility for the clipboard, effectively restarting it, and that worked!

If your clipboard copy/paste is not working on your remote machine, to save you the same time and frustration, here is how I resolved the issue:

  • Click on Start and type “Run”. Press Enter. (You can also press the Windows Key + R combination to open the Run window).
  • In the Run window, type cmd and press enter or click OK.
  • When the black command prompt window opens, type the following:
  • taskkill /f /im rdpclip.exe
  • Press enter.
  • Close the command prompt (black) window.

By entering this command, you are forcing the shared clipboard to close and restart itself. Hopefully this helps you out!

View Two Tabs at Once in Excel – One Workbook Side By Side

Posted on by Bb

Are you aware that you can view two tabs at once in Excel, side by side for a single Excel workbook/file?  This is especially helpful if you are working with a formula that affects another tab, whether checking for accuracy or troubleshooting.  It’s also great if you are running what-if scenarios such as in a sensitivity analysis and you would like to keep an eye on the values in two tabs.

Adding a second window in Excel to see two tabs at once:

  1. On the menu bar, click View.
  2. Click New Window.
  3. Position the two windows however you’d like.
  4. Note that it is still one file, so both windows will update when you make a change.  You can save from either window.

Alternatively, if you love keyboard shortcuts like I do, simply use this combination: ALT + W, then N.

Open Two Tabs in Excel
View menu for Excel

Here is an example of how it looks for this sample loan calculator file:

Open New Window Side By Side in Excel

Simple solutions are the best! I truly hope this helped you out! Please consider bookmarking this site and subscribing. I add new content often! Thanks!

Also, check out Amazon for some amazing resources: https://amzn.to/3IoE4O4!  Disclosure: this is an affiliate link, so I may earn a very small commission if you decide to make purchase.  Thanks for supporting my blog!

If I helped you today, please consider leaving a tip via PayPal to help cover blog costs! Thanks!!

Subscribe for more great content!

rank in Access

Rank in Access Query (Sales Ranking)

Posted on by Bb

A common request I get these days is for ranking of sales in Access by region, state and item combination for analysis and space management.  In my business, we have some overlap, where a state can be part of two separate areas or regions, for example Southern CA vs. Northern CA.  It’s helpful for us to show separate rankings for each region and state combo, as the results often tell a different story for each.

The Pass-Through query below enables me to determine the ranking at each of these levels.  By entering as a Pass-Through query, you can utilize the Rank and Partition SQL commands.  After running this query, I bring the data into Excel for further analysis, but this query is a good starting point for any similar analyses.

Here is the full Access pass-through query, color coded for the explanation below:

Select Region, State, Product_ID, SumSaleQ, StoreCount,Rank
FROM (Select Region, State, Product_ID, SumSaleQ, StoreCount, Rank() over (Partition By state, Region Order by SumSaleQ DESC)as rank
FROM (Select s.Region, s.state, m.Product_ID, sum(m.sale_quantity) as SumSaleQ, count(distinct(m.site_num)) as StoreCount
FROM Store_monthly_sales m, FR_Site_Master s
WHERE m.Site_num = s.Site_num AND m.accrual_month in (‘202007′,’202006′,’202005’)
GROUP BY s.Region,m.Product_ID,s.state)
WHERE SumSaleQ >5)
WHERE rank <205
Order by State ASC, Region Asc, Rank Asc

Explanation

Query 1

Diving right in, since we are measuring at multiple levels, we have some nested queries.  Working from the inside out, my first nested query is this one:

(Select s.Region, s.state, m.Product_ID, sum(m.sale_quantity) as SumSaleQ, count(distinct(m.site_num)) as StoreCount
FROM Franchise.Store_monthly_sales m,Franchise.FR_Site_Master s
WHERE m.Site_num = s.Site_num AND accrual_month in (‘202007′,’202006′,’202005’)
GROUP BY s.Region,m.Product_ID,s.state)

Query 2

We then nest that within this query (the WHERE clause is optional – I am dealing with sales in the thousands, and want to exclude items with a sales quantity less than 5.  Note the Rank() over and Partition By Commands here:

(Select Region, State, Product_ID, SumSaleQ, StoreCount, Rank() over (Partition By state, Region Order by SumSaleQ DESC)as rank

FROM …

WHERE SumSaleQ >5)

Query 3

The final nesting and main query is selecting the rank from the second query, along with the other pertinent attributes.  The WHERE clause is again optional, I am looking to limit my results to the top 200 results only for each region and state combination. I use 205 as the figure to allow for ties.  The Order by is also optional.  I like to order the results by state, region and then rank ascending.

Select Region, State, Product_ID, SumSaleQ, StoreCount,Rank

FROM …

WHERE rank <205
Order by State ASC, Region Asc, Rank Asc

Full Query (no color coding)

Select Region, State, Product_ID, SumSaleQ, StoreCount,Rank
FROM (Select Region, State, Product_ID, SumSaleQ, StoreCount, Rank() over (Partition By state, Region Order by SumSaleQ DESC)as rank
FROM (Select s.Region, s.state, m.Product_ID, sum(m.sale_quantity) as SumSaleQ, count(distinct(m.site_num)) as StoreCount
FROM Store_monthly_sales m, FR_Site_Master s
WHERE m.Site_num = s.Site_num AND m.accrual_month in (‘202007′,’202006′,’202005’)
GROUP BY s.Region,m.Product_ID,s.state)
WHERE SumSaleQ >5)
WHERE rank <205
Order by State ASC, Region Asc, Rank Asc

Conclusion

I hope this post will help you in your efforts to rank in Access.  It can be a bit of a challenge, but hopefully the breakdown of the steps here will help you achieve your ranking goals.  Please comment any questions, feedback, or what you would like to see next!  Share this post if you feel it would help someone else! 🙂

If I helped you, please consider buying me a coffee via PayPal! Thanks!!

rank in Access

Solved: Pass a Parameter to a SQL Query in Power Query

Posted on by Bb

I’ve done a fair amount of research around dynamic and parameterized queries, specifically around an Excel query parameter, as in trying to pass a parameter to a SQL query in Power Query.  Here’s the very quick and easy way to do this!

This exact solution works in Excel. Creating a dynamic SQL query with a parameter in Excel allows for amazing flexibility. The use cases are endless and can benefit so many types of Excel work across industries – if you are querying any data source, such as a database, even an Access database, then parameterized querying in Excel can truly simplify your workflow. I am going to cover SQL queries specifically, but please comment below if you would like a tutorial on using a parameter with other data sources. Nearly anything is possible!

You can also easily use parameters in Power BI, but the process for creating the parameter itself is slightly different – I will cover that in a future post.  The query construction is the same in most cases though. Please note that this post assumes you are at least moderately familiar with SQL querying and Power Query. Your mileage may vary depending on your situation.

If you found this post because you are encountering a firewall error when incorporating a parameter, skip right on to that section below (click here) for the solution. After much trial and error, I have found a way that works to overcome this issue harnessing the power of query folding. 

The Solution: Passing a Parameter to a SQL Query

In my example, “SParameter” is the name of the parameter I am using, and it represents a store number (retail location identifier). The related field in my SQL table is [Store].

Create the parameter:

  • In any worksheet, add a new table, with a single record and column.  I highly recommend the Header clearly clarifies what the parameter is so there is no confusion in identifying it.
  • Add this table into Power Query by selecting the table, then Data > From Table/Range. 
  • When the query editor opens, if necessary, change the data type (in my case I need it to be text).  This is important!
  • Right click on the record and select “Drill Down”.  Since you have only a single record, this step will automatically create a parameter.  
  • In the properties, I name this SParameter (you may enter the name of your choice for your parameter).  Note that the name is case sensitive.

This is how the table is situated in my worksheet (1 is the parameter/the value I will change as needed):

This is how it looks in Power Query after all steps above were completed (I named the column from Enter Store: to Store to make it cleaner (this is an optional step):

Open or Add your SQL Server Query in Power Query

Add your query into the Advanced Editor.

let
Source = Sql.Database(“your connection name”, “your database name”, [Query=”

your query goes here

“])

Create the SQL query with your parameter:

Here’s my simple query example to demonstrate how to build the query with the parameter. Please substitute your table name and fields, as well as your parameter name.

With the parameter in the WHERE clause

“Select  * FROM StoreDetail.Store WHERE Store =” &SParameter&”“

  • Simply build your unique SQL statement in Power Query following my example above, adding your parameter into the query with quotes and the & (ampersand) sign surrounding the parameter name.
  • Check the results and modify as needed.
  • If your query has no additional clauses or criteria, simply put quotes and one & sign ahead of the parameter, and then one after followed by two double quotes to close the query statement.

 Additional criteria in the WHERE clause

Here is an example where I have additional criteria in mine, filtering on the OpenDate field; after the second quote, add a space and then continue on with the rest of your query.

Select  * FROM Storelist WHERE Store =” & SParameter &” AND OpenDate < GetDate()-365“

This is an example of the properly formatted parameter looks in the Advanced Editor in Power Query (I have built out my query further with the Group By clause – this is optional if it doesn’t apply in your case).

If you are running into issues with the query, you may need to add in single quotes surrounding the parameter (inside of the double quotes – “WHERE Store =‘” & SParameter & “‘;”), or possibly # signs surrounding your parameter inside of the double quotes, if your parameter is a date.  You may have to test with and without these depending on your unique scenario.  

If the query is successful, you can proceed with saving it or adding in additional steps in Power Query if you are further filtering or transforming the resulting data you pulled.

BONUS Information – if you are wondering what GetDate() is, this is the MS SQL current system date, so in a way this is technically a dynamic parameter as well.  In my case, the query will only return stores with an open date that is older than 365 days based on today’s date.  You may find this useful for your query as well if you are looking for dynamic dating.

After updating your parameter in the worksheet table, Select Refresh All from the Data menu and your updated query will run and return the results accordingly!

Managing Privacy Levels in Power Query/Excel

VERY IMPORTANT: If you are sharing your workbook with others, you may need to edit the Privacy Levels in order for the queries with parameters to work for them. Read on for these important steps.

In Excel Power Query, there are security measures in place to help prevent data from being shared unintentionally. When combining query sources by using the parameter (a value in your Excel file with a SQL server source), many times this security comes into play.

If other users are having an issue refreshing the query/queries where you have added a parameter into the SQL query, please have them follow these steps. I actually copy and paste this right into an Instructions tab of each of the shared workbooks containing parameters that I create for my clients and for colleagues.

  • Go to Data, click Queries & Connections.
  • Your query list will show up in the panel on the right.
  • Right click any query in the list and choose Edit.
  • A window will open. Click File, then Options and settings.
  • Choose Query Options.
  • Click Privacy, which is on the menu on the left, at the bottom.
  • Select the radio button for the Ignore the Privacy Levels and potentially improve performance option.
  • Click OK to save.
  • Click Close & Load in the Power Query Editor window (at the top) to exit the settings.
Parameter to a SQL query (privacy settings)

Troubleshooting/Alternate Method – Firewall issue

If you run into this dreaded error: “Formula.Firewall: Query ‘SParameter (2)’ (step ‘Filtered Rows’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.”, then you will need to use this alternate method.

If you received the firewall error above, you can still use your parameter, but you will instead need to use the parameter as a filter in a subsequent step.  Now you may be possibly thinking – my table has thousands (or even millions) of records and I don’t want them all pulling in – don’t worry, this is the beauty of the query folding process when it works properly in Power Query.

Without the parameter in the WHERE clause

Select * FROM StoreDetail.Store WHERE OpenDate < GetDate()-365

  • Write your SQL statement in Power Query as noted above.
  • When the Query Editor returns the columns and record sampling, for the field you will be using your parameter, filter with any single value (Text Filters > Equals). This is simply a placeholder, to create the Power Query M formula for the next step.
  • You will now see that filtering in the formula bar. 
  • Replace the placeholder value with the name of your parameter, removing any quotes from the placeholder.  This is how it looks for me: = Table.SelectRows(Source, each [Store] = SParameter).
  • Power Query is smart enough to modify its native query to use the parameter, so it’s not going to pull in the millions of records and then filter after the fact.  Success!!  This is the power of Query Folding!

Why Can’t You Include the Parameter in the SQL Statement?

In the SQL statement, in most cases we will want add the store number/SParameter as part of the criteria in the select statement.  It may seem counter intuitive, but we cannot always successfully put the parameter directly into the SQL code/query. The challenge is that you are combining data sources and at this time, this is not allowed due to the built in security (SParameter portion is local, SQL portion is external).  Unfortunately some organizations have policies that disallow this. Sometimes it is an Excel version issue.

I truly hope this post helped you out.  If so, please feel free to leave a comment below letting me know so, and if you’d like, add what you’d like me to cover in a future post. Also, feel free to share this with someone else who may find it useful.

Do You Need Personalized Help and Custom Solutions?

If you get stuck or you would like to explore solutions and automation possibilities, please can reach out to me for help as I do offer consulting services as time allows.  I have over 20+ years’ of expert level experience delivering excellent, custom, strategic solutions in Excel, BI, Access, SharePoint and more. 

I have been called a guru and hero more times than I can count, as I am a great listener and truly have a knack for asking the right questions to understand unique business challenges. I am very passionate about crafting tools and processes that work for users of all levels and experience. 

Reach out today and let’s discuss how I can help you and your business!

I also offer one-on-one tutoring for customized learning and upskilling. Visit my consulting page and send a message if you are interested.

Other Excel Resources

Also, consider checking out some great Excel resources on Amazon!  Disclosure: this is an affiliate link, so I may earn a small commission if you decide to make a purchase, which will help cover the cost of hosting this website. 

Please bookmark and subscribe!  I am actively working on adding new, relevant content to help others out! Thanks so much!

Subscribe via Email and Stay Up to Date!

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Buy Me A Coffee/Support the Blog 🙂

If I helped you, please consider leaving a tip via PayPal to support the blog! Thanks!!

Thanks so much for supporting my blog and for helping others too!

Convert Date in Access – Serial Number, Text String, First of Month, End of Month, etc.

Posted on by bonbon

Convert Date In Access – Serial Number, Text Date, First of Month, End of Month/last day of month, Date to Text. It is fairly common when compiling date data that we need to convert it, whether for use in other apps (like Excel), outputting in a nicely readable format (Word, PDF, etc.) or to enable joins to other tables, possibly even in other systems. 

MS Access will not automatically convert dates into different types from a menu, so I’ve created this handy post to quickly and easily assist you with doing so quickly.

INSTRUCTIONS

To begin, you will create a new field in your query, and then continue with the conversion below that applies in your scenario. 

Helpful hint: Substitute [yourdate] with the name of the field that you are converting. In the images, the date in my table is called Journal_Date, so this is the one I am converting in each scenario. You should use the actual field name in your table.

To convert a date in the date/time format to the serial number (7/1/2019 to 43647), use:

Cdbl ([yourdate])

convert date in access to serial

To convert a date in the serial number format to date/time format (43647 to 7/1/2019), use:

Cdate ([yourdate])

convert date in access - serial to date

To convert a text or string date to the date/time format, try:

DateValue ([yourdate])

To convert a date to the first day of the month:

DateSerial (year([yourdate]),month([yourdate]),1)

Access first of month conversion

To convert a date to the last day of the month/end of month:

DateSerial (year([yourdate]),month([yourdate])+1,1)-1

Access end of month conversion; Access last date of month conversion

You can also convert a date/timeformat to a string – here is one example:

Format([yourdate],”mmmmyyyy”)

This would render 7/1/19 in your date field as July2019.

Access Date to Text Format Convert Date in Access

More information on the Format function and syntax can be found on the Microsoft Office Support site if you are looking to output the text differently.

Conclusion

Please feel free to contact me or comment if there are other tutorials/how-to articles you would like to see. I’d love to help you out.

If I helped you, please consider leaving a tip via PayPal to support the blog! Thanks!!

Subscribe for more great content!

multiply an entire column

Multiply an Entire Column by a Number in Excel (without using a formula)

Posted on by Annie D

Are you looking to multiply an entire column or range by a number? Get ready for a quick and easy tutorial!

You could always use another column and have the formula to do this, but this way is quick and easy if you do not plan on changing the values.

  1. All you need to do is have the number that you want to multiply in a cell (any cell).
  2. Now copy this cell and paste as special in the cells/range/column where you want to multiply it.
  3. In the Paste Special dialog box, choose the multiply option (in the Operation section) and click OK.  Your column/cells/range should be multiplied!
  4. If desired, can delete the cell that has the value which you multiplied by.

The resulting data is static/fixed so there’s no need to change any formula to value after multiplying using the paste special function.

Multiply An Entire Column

As always, I hope this helped you! Simple solutions are the best!

If I helped you, please consider buying me a coffee via PayPal! Thanks!!

Subscribe for more great content!

Delete Every Other Row or Column in Excel (using Formula or VBA)

Posted on by Bb

Today we are going to explore various ways to delete every other row or delete every other column in Excel. VBA macro code is included to help you on your way if that is the path you choose.

Additionally, we will also cover how to delete every third/fourth/fifth row or column in Excel.

To delete alternate rows, you can use a helper column and use a formula that helps you identify alternate rows. This could be done by using the ISEVEN function that checks each row number and returns TRUE if the row is even and false if it isn’t.

Once you have this, you can easily filter the rows with FALSE in the helper columns and delete these.

In case you want to delete every third row, you need to use the MOD function to identify every third row. Once you have it, you can easily filter and delete every third row or every fourth row.

I also cover a method to use a simple VBA code to delete alternate rows in Excel.

Below is that VBA CODE TO DELETE ALTERNATE ROWS:

Sub Delete_Every_Other_Row()
Dim Rng As Range
Set Rng = Application.InputBox("Select the Range (Excluding headers)", "Range Selection", Type:=8)
For i = Rng.Rows.Count To 1 Step -2
If i Mod 2 = 0 Then
Rng.Rows(i).Delete
End If
Next i
End Sub

When it comes to deleting alternate columns, you cannot filter these. You can instead sort and bring all those columns together that you want to delete.

I cover a simple method that uses the MOD function to identify alternate columns and then sort these from left-to-right to bring these together. Once you have these in one place, you can select and delete these.

And there is also a VBA code that you can use to delete alternate columns.

Below is the VBA CODE TO DELETE ALTERNATE COLUMNS

Sub Delete_Every_Other_Column()
Dim Rng As Range
Set Rng = Application.InputBox("Select the Range (Excluding headers)", "Range Selection", Type:=8)
For i = Rng.Columns.Count To 1 Step -2
If i Mod 2 = 0 Then
Rng.Columns(i).Delete
End If
Next i
End Sub

Subscribe to this YouTube channel to get updates on Excel Tips and Excel Tutorials videos – https://www.youtube.com/c/trumpexcel

You can find a lot of useful Excel resources on the following site: https://trumpexcel.com/

#Excel #ExcelTips #ExcelTutorial

View on YouTube

Power Query Filter Rows by NOT Contains Criteria – Single Formula Solution

Posted on by Annie D

Learn how to filter a table based on NOT Contains Criteria. Download an example workbook here.

Easy step by step instructions below.  See a single formula solution using the functions Splitter.SplitByAnyDelimiter, List.Count and Table.SelectRows. Amazing formula solution from Power Query Poet, Bill Szysz.

Please watch the video if you’d like for a guided walkthrough and also another method that can be useful for multiple tables.  

Criteria Table

  1. Create a new, separate table with the list of terms you will want to exclude. Name the table “NoCriteria”.
  2. Add the excluded item (NoCriteria) table to Power Query – click within table, under Data menu, choose From Table/Range, which is in the Get and Transform data section.
  3. In the Power Query window, select the Transform menu and click convert to list.
  4. Under the File menu, choose Close and Load To, then choose Connection Only.

Building the Filtering via Power Query

  1. Add or create the list/table that will ultimately be filtered.
  2. Click any cell within the table that will be filtered. Add the excluded item table to Power Query (click within table, under Data menu, choose From Table/Range, which is in the Get and Transform data section).
  3. In the Power Query window, click the Add Column menu, and select Custom Column.
  4. In the window that opens, type this:

    = Table.SelectRows(#”Changed Type”, each List.Count (Splitter.SplitTextByAnyDelimiter(NoCriteria)([PRODUCT_NAME]))=1)

  5. Change [PRODUCT_NAME] in that text to your own column in the table that you will be filtering on if it is different.
  6. Select the statement you have typed in and copy it (you will need to paste this formula in a following step).
  7. Click OK. You will see that the formula you typed was changed by the program and a column was added.
  8. To change the formula back, click the menu bar, highlight the entire text and then replace by pasting in the formula you copied. Hit enter.
  9. The extra column should be removed and the table should be filtered on you criteria from the NoCriteria table.
  10. Click the File menu, choose Close and Load To, then choose where you would like the newly filtered table loaded to.

Any time changes are made to the exclusion list, you will need to refresh the filtered table. Simply right click any cell within the filtered table, and select Refresh.

 

Bonus – Filtered table with the excluded items only (not shown in video)

You can additionally create a filtered table that only includes the terms in your NoCriteria table!  

  1. To do this, go into Power Query.  Right click on your filtered table and click Duplicate.
  2. In that new table, you will very slightly change the existing formula in your Power Query to not equal one (see orange text):

    = Table.SelectRows(#”Changed Type”, each List.Count (Splitter.SplitTextByAnyDelimiter(NoCriteria)([PRODUCT_NAME]))<>1)

  3. Hit enter.  The table should now only filter on the items in your exclusion list, instead of including them.
  4. Click the File menu, choose Close and Load To, then choose where you would like the newly filtered table loaded to.

View on YouTube

FILTERING-OUT-CONDITIONAL-LIST-1-1Download

Create A Gantt Chart In Power BI With A Matrix Visual

Posted on by Annie D

Managing projects effectively requires clear visuals that communicate timelines and task progress at a glance. While Power BI offers many visuals, building a Gantt chart using the built-in Matrix visual is a powerful technique for showing detailed project timelines without needing custom visuals. This blog post walks through the key concepts and steps for creating a Power BI matrix Gantt chart, perfect for project managers and data analysts alike.

What is a Matrix Gantt Chart in Power BI?

A Gantt chart visually represents project schedules by showing tasks as bars mapped over calendar dates. Unlike using custom Gantt chart visuals, you can cleverly use Power BI’s matrix visual combined with DAX formulas and conditional formatting to mimic a Gantt chart. This approach is advantageous because it leverages native visuals without extra installation, is highly customizable, and integrates seamlessly with your existing data models and filters.

Why Use a Matrix Gantt Chart?

  • Native Power BI Visual: No need for third-party downloads or licensing.
  • Dynamic and Interactive: Integrates with slicers and filters for dynamic timeline updates.
  • Detailed Task View: Offers drill-down capabilities across project hierarchies.
  • Full Control Over Design: Customize colors and styles through conditional formatting rules.

Step-by-Step Guide to Creating a Power BI Matrix Gantt Chart

  1. Prepare Your Data Model
  2. Your dataset should include at least these columns:
    • Project or Task Name
    • Start Date
    • End Date or Duration(Optional)
    • Task Status or Category
    • You also need a Date table in your data model to serve as a timeline backbone.
  3. Establish Relationship sConnect your Date table to your Project table using the Start Date (and possibly End Date) fields to enable time intelligence across visuals.
  4. Create a Matrix Visual
    • Place Project Name (and subcategories if needed) in the Rows.
    • Place Date from the Date table in the Columns.
  5. Create a DAX Measure to Highlight Task DurationWrite a measure that returns 1 if a given date falls within the task start and end dates and 0 otherwise. This will be the basis for your Gantt bars.Example simplified logic: TaskActive = IF( SELECTEDVALUE('Date'[Date]) >= MIN('Projects'[StartDate]) && SELECTEDVALUE('Date'[Date]) <= MAX('Projects'[EndDate]), 1, 0 )
  6. Apply Conditional Formatting Use conditional formatting on the Matrix’s Values field. Format the background color to:
    • Show a distinct color (e.g., gold or blue) when the TaskActive measure equals 1. Show a lighter or neutral color when 0.
    This creates the visual bars of the Gantt chart within the matrix cells.
  7. Enhance Your Visual
    • Turn off subtotals for clarity.
    • Use slicers to filter projects or timelines dynamically.
    • Add tooltips with task details.
    • Customize colors by task status or category using additional measures and formatting rules.

Benefits of Using a Matrix Gantt Chart in Power BI

  • Cost-effective: No licensing or external visuals needed.
  • Integrated experience: Aligns with your Power BI reports and dashboards perfectly.
  • Scalable: Works well from small projects to complex multi-phase portfolios.
  • Customizable: You control colors, interactivity, and granularity.

By leveraging Power BI’s matrix visual combined with smart DAX measures and conditional formatting, you can build a robust, customizable Gantt chart tailored to your project management reporting needs. Start experimenting with your project data today and unlock richer timeline insights right inside Power BI!

Other Options/Resources

For those interested in alternative approaches, there are also custom Gantt chart visuals available in Power BI Marketplace, but the matrix method provides unmatched flexibility and control for many project reporting scenarios.

Delete Rows Based on a Cell Value (or Condition) in Excel [With and Without VBA]

Posted on by Annie D

Managing data efficiently in Excel often means removing unwanted rows that meet certain criteria—such as rows with specific text, dates, numbers, or even partial matches. Whether you’re cleaning up a small spreadsheet or preparing a large dataset for analysis, understanding how to delete rows based on cell values will help you keep your data tidy and relevant. This guide explores multiple techniques to remove rows in Excel according to specific cell values, making your data management tasks faster and more accurate regardless of your proficiency with Excel features or VBA automation.

This tutorial explains several methods to remove rows from an Excel worksheet based on the value in a specific cell or according to set conditions.

Here are four different approaches you can use to delete rows depending on their cell values:

  1. Using Filters:
    Apply a filter to your data, select the criteria you want to remove (for example, rows where “Status” is “Inactive”), and delete all the filtered rows at once.
  2. Sorting Data:
    Sort your data by the column you want to filter (e.g., sort by “Department” so all “Sales” records are grouped together) and then delete all the matching rows in one go.
  3. Finding Cells with Specific Values:
    Use Excel’s “Find” feature to locate cells with a value like “Expired”, select those rows, and delete them all together.
  4. VBA Automation:
    Automate row deletion by using a VBA macro that filters and deletes based on your criteria (e.g., remove all rows where “Order Status” is “Cancelled”).

Tip:
Choose the method that best fits your dataset’s structure and your workflow. Remember, deleting a row removes everything in that row—including all data to the left and right. If you only want to clear certain cells but keep the row and other information, consider using the filter and dummy column trick, or manually clearing cell contents instead of deleting the row.

Wildcard Matching Example:
With Find and Replace, you can use wildcards for powerful matching. For instance, to find every region ending with “East” (such as “North-East” or “South-East”), type “*-East” (the asterisk stands for any sequence of characters).

Example VBA Codes

Delete All Rows Where “Status” is “Inactive”:

textSub DeleteRowsWhereInactive()
    ActiveCell.AutoFilter Field:=3, Criteria1:="Inactive" 'Assumes the Status column is column 3
    ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete
End Sub

Prompt User before Deleting Rows Where “Order Status” is “Cancelled” Without Deleting the Entire Row:

textSub DeleteCancelledStatusCells()
    Dim MsgboxAns As Integer
    ActiveCell.AutoFilter Field:=4, Criteria1:="Cancelled" 'Assumes Order Status is column 4
    MsgboxAns = MsgBox("Are you sure you want to delete these cells?", vbYesNo + vbQuestion)
    If MsgboxAns = vbYes Then
        ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete (xlShiftUp)
    End If
End Sub

Delete Rows in an Excel Table Where “Department” is “Support”:

textSub DeleteRowsinTableForDepartment()
    Dim Tbl As ListObject
    Set Tbl = ActiveSheet.ListObjects(1)
    ActiveCell.AutoFilter Field:=2, Criteria1:="Support" 'Assumes Department is column 2
    Tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
End Sub

**#Excel #ExcelTips #A #ExcelTutorial

Let me know if you need more tailored code or examples for your data!

Cross Selling Matrix Deep Dive – Power BI & DAX Tutorial (Market Basket)

Posted on by Annie D

Understanding the Cross Selling Matrix in Power BI

A cross selling matrix is a powerful visualization used in sales analytics to identify which products are often purchased together by the same customers over a selected period. This form of basket analysis helps businesses uncover cross-selling opportunities, promotional ideas, boost revenue, and optimize product strategy.

The video demonstrates not just the process of creating a cross selling matrix in Power BI, but dives deep into the crucial concepts of DAX, context transition, and relationship management needed to generate accurate, actionable insights.

Key Steps and Concepts

1. Foundational Understanding: Context in Power BI & DAX

  • Context determines how your formulas and visuals behave. The row and column headers of your matrix create unique contexts for every cell, impacting which data is aggregated or filtered.
  • Proper understanding of context ensures your DAX calculations are returning meaningful results for each product pairing in the matrix.

2. Core Calculation: Customers Who Purchased Both Products

  • The goal is to find out, for any intersection in the matrix, how many customers bought Product A (row) AND Product B (column) within the selected date range.
  • This is done by creating two tables:
  • Table 1: All customers who bought Product A.
  • Table 2: All customers who bought Product B.
  • The INTERSECT function is then used to find customers common to both tables.
  • The final result is a COUNTROWS(INTERSECT(…)), revealing the number of unique customers who purchased both products.

3. DAX Techniques Used

  • VALUES(): Used to dynamically return a list of customers filtered by the current context (product, time frame, etc.).
  • CALCULATETABLE(): Allows creation of virtual tables filtered by specific product or comparison product context.
  • TREATAS(): Establishes virtual relationships between tables where no direct relationship exists, vital for comparing separate product lists.
  • ALL() or ALLEXCEPT(): Used to remove or adjust existing model relationships temporarily, isolating the proper comparison across products for accurate results.

4. Supporting Table for Comparison

  • To evaluate pairwise cross-selling (row vs column), a comparison products table is created, usually replicating your products dimension but used solely for comparison logic.
  • This table is not physically related to the sales table, so relationships are built on-the-fly in DAX using TREATAS.

5. Dynamic Filtering and Analysis

  • The entire technique is dynamic, meaning selecting different dates or filters in your Power BI report instantly recalculates the matrix.
  • This adaptability makes the matrix valuable for both exploratory analytics and operational dashboards.

Why Build a Cross Selling Matrix?

  • Reveal Product Affinities: Quickly see which items are often bought together, ideal for bundle promotions and recommendations.
  • Drive Sales Strategies: Identify which products could benefit from cross-promotion or upselling.
  • Customer Insight: Understand multi-product purchasing behavior within your customer base.

Example DAX Pattern for Purchased Both Products

Purchased Both Products = 
VAR Customers_ProductA =
    VALUES(Sales[CustomerID]) // For current row product context
VAR Customers_ProductB =
    CALCULATETABLE(
        VALUES(Sales[CustomerID]),
        TREATAS(VALUES('Comparison Products'[ProductID]), Sales[ProductID])
    )
RETURN
    COUNTROWS(INTERSECT(Customers_ProductA, Customers_ProductB))
  • Replace column/table names as per your own model.
  • Adjust context and relationships as necessary for your specific data schema.

Takeaway

By mastering this advanced cross selling matrix technique and the supporting DAX concepts (like context, table functions, and virtual relationships), you empower yourself to unlock powerful, nuanced insights into customer behavior and product performance using Power BI.

If you’re keen to further enhance your skills on this, Enterprise DNA is a superb resource! Check them out and watch their video on this below! 🙂

[1] https://www.youtube.com/watch?v=iZJz30LSik4