Forum Discussion
Dynamic matrix with categories/subcategories
In Power BI, you can achieve the desired functionality of displaying Category 1 only if Subcategory 2 has more than 1 entry for the specific year chosen in a filter by using measures, slicers, and conditional formatting. Here's a step-by-step guide on how to do this:
Data Model: Ensure that your data model contains the necessary columns, including the year, Category 1, and Subcategory 2.
Create Measures:
- Create a measure that calculates the count of entries in Subcategory 2 for the selected year. You can use the COUNTROWS function for this. For example:
Subcategory2 Count = COUNTROWS(FILTER(YourTable, YourTable[Year] = SELECTEDVALUE(YearFilter)))
Create another measure to determine if Category 1 should be displayed or not based on the count of Subcategory 2. This measure could be a simple IF statement. For example:
Display Category1 = IF([Subcategory2 Count] > 1, [Category1], BLANK())
Slicer for Year:
- Create a slicer that allows the user to choose the year. This slicer should be connected to the relevant table or column containing the year information.
Matrix Visualization:
- Create a matrix visualization in your report.
- Place the "Year" field in the Rows area.
- Place the "Display Category1" measure in the Values area.
Conditional Formatting:
- To hide the Category 1 values when the count of Subcategory 2 is less than or equal to 1, you can apply conditional formatting to the "Display Category1" measure. Here's how to do it:
- Click on the measure in the Values area of the matrix.
- Go to the Format pane.
- Under "Conditional formatting," select "Color scale."
- Set the color for values less than or equal to 1 to be the same as the background color (e.g., white) to effectively hide them.
- To hide the Category 1 values when the count of Subcategory 2 is less than or equal to 1, you can apply conditional formatting to the "Display Category1" measure. Here's how to do it:
Now, when a user selects a specific year in the slicer, the matrix will display Category 1 only if Subcategory 2 has more than one entry for that year. If Subcategory 2 has one or no entries, the Category 1 value will be hidden due to the conditional formatting.
This approach allows you to dynamically control the visibility of Category 1 based on the count of entries in Subcategory 2 for the selected year.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
Dear 123abc ,
Thank you very much for the very detailed reply. This helps me definitely along the way. I realize now that I should have added some more information to my problem.
Behind the year data in the pictures I can have multiple transaction (by date) aggregating up to the amounts seen in the pictures. So transactions can be multiple but only if Subcategory2 are more than 1 for the Category1 it should show in the matrix. Also the filter used in the matrix needs to be able to be dynamic for Year, quarter and month. Hope it clarify the complexity a bit more.Tried to explain it below
Thank you!I see that your requirement involves dynamic filtering for Year, Quarter, and Month, and you want to display Category1 in the matrix only when Subcategory2 has more than one transaction within the selected time frame. To achieve this, you may need to use a more advanced approach using Excel or similar spreadsheet software. Here's an extended guide to address these additional complexities:
Data Structure: Ensure your data includes categories, subcategories, transaction dates, and transaction amounts.
Create a Pivot Table:
- In Excel, select your data range, go to the "Insert" tab, and click "PivotTable." In Google Sheets, go to "Data" and select "Pivot table."
- In the PivotTable Field List, drag "Category1" and "Subcategory2" to the "Rows" area.
- Drag "Transaction Date" to the "Filters" area.
Filter by Date:
- Apply a filter to the "Transaction Date" field to select the desired Year, Quarter, or Month.
Subcategory2 Count:
- Add the "Subcategory2" field again to the "Values" area of the PivotTable.
- Change the aggregation function for "Subcategory2" to "Count" to count the number of transactions.
Filter by Subcategory2 Count:
- Apply a filter to the "Count of Subcategory2" to only display Category1 when the count is greater than 1 for the selected Year, Quarter, or Month.
To make the filter dynamic for Year, Quarter, and Month:
Create Helper Cells for Date Selection:
- In a separate area of your spreadsheet, create cells where you can select the Year, Quarter, and Month. These can be dropdown lists or input cells.
Use Helper Cells for Filters:
- Change your "Transaction Date" filter in the PivotTable to refer to the values in the Year, Quarter, and Month selection cells. This allows you to dynamically filter data by changing the values in these cells.
With this setup, you can select the Year, Quarter, or Month in your helper cells, and the PivotTable will automatically update based on your selection. The Category1 will only be displayed if Subcategory2 has more than one transaction within the selected time frame.
Keep in mind that the exact steps may vary depending on your spreadsheet software, but this approach allows for dynamic filtering and the display of Category1 based on your specified criteria.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
Thank you again 123abc
Its actually not transaction that should be the determinator if it should be shown or not in the matrix. But instead if there are more than one Subcategory2 in the Category1 for the chosen time period. As shown in my previous example where Charlie and Delta should not be shown even though they have 39 respectively 15 transactions. But Category1 should show even though they only have one transaction per subcategory. The important part is the Subcategory2 column within the selected period. Less than one Subcategory2 for each specific Category1 = not to be shown, more than 2 Subcategory2 for each Category1 = shown
