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:

1. Data Model: Ensure that your data model contains the necessary columns, including the year, Category 1, and Subcategory 2.

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())

1. 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.

2. 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.

3. 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.

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.

Hi AlexXandyr 

You can refer to the following solution.

Sample data 

Sample slicer data

The relaionship

Create a measure

Measure =
VAR a =
    COUNTROWS (
        FILTER ( ALLSELECTED ( 'Table' ), [Category] IN VALUES ( 'Table'[Category] ) )
    )
RETURN
    IF (
        ISFILTERED ( Slicer[Year] ),
        CALCULATE ( SUM ( 'Table'[Amount] ), FILTER ( 'Table', a > 1 ) ),
        CALCULATE ( SUM ( 'Table'[Amount] ) )
    )

Then put the field to the matrix visual

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.