Matrix Powerbi: How to hide a row (i.e. a measure) if all the months have no value for that measure.

[CBO2404]

I have a Power BI report (with a live connection to a dataset) that contains a matrix visual.

The Columns field contains the 12 months of this year, and the Values section includes about 20 different measures.

I enabled "Switch to rows" in the format values options, so each measure appears as a separate row.

Now I want to hide a row (i.e. a measure) if all the months have no value ( blanks) for that measure.

Turning off "Show on blank rows" has no effect.

Does anyone have an idea how this can be solved? ChatGPT couldn't provide a working solution, so I'm hoping one of you can help.

[CBO2404]

How can I hide SUMC???

My Example table: 

My Measures:

SUMA = CALCULATE(SUM(Example[valueA]))

SUMB = CALCULATE(SUM(Example[valueB]))

SUMC = CALCULATE(SUM(Example[valueC]))

MeasureVisibility =

IF(ISBLANK(CALCULATE([SUMA])) &&ISBLANK(CALCULATE([SUMB])) &&ISBLANK(CALCULATE([SUMC])),0,1)

My Visual and filters:

[v-nmadadi-msft]

Hi @CBO2404 ,

Please include the DAX that you have just created in the values column

Once that is done you will find an additional column in your matrix/table

You can use Visual level filter to filter out the 0 values.

Attaching the PBIX where I tried out the above mentioned dax as reference.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank you

[CBO2404]

Thanks for your effort and your example. However, I have the 'Switch values to rows' option turned on, which means the values are placed in the rows. In that case, your solution doesn't work.

[rohit1991 · Accepted solution]

Hi @CBO2404 ,
This is a common challenge when using a matrix visual in Power BI, especially with a live connection where calculated columns or tables aren't an option. Since you’ve already enabled "Show values as rows" and turned off "Show on blank rows" (which typically only applies to rows from the data model, not measures), the matrix will still display all measures, even if they are entirely blank across the columns. Unfortunately, Power BI does not natively support dynamic hiding of measures (rows in this case) based on all-blank results. 

A common workaround is to create a parent measure for each of your measures that checks whether it returns any non-blank values across the time range. Then, you could use a disconnected table to list the measure names and only show those that meet the non-blank condition using a slicer or dynamic calculation group. 

However, this approach requires changes to the model, which isn’t feasible with a live connection. In that case, your best option may be to request model changes on the dataset side or use Power BI Desktop with an import mode if possible, where more advanced DAX and visual-level filtering techniques can be applied.

[bhanu_gautam]

@CBO2404 Create a new measure:

DAX
MeasureVisibility =
IF(
ISBLANK(CALCULATE(SUM([YourMeasure1])) &&
ISBLANK(CALCULATE(SUM([YourMeasure2])) &&
... &&
ISBLANK(CALCULATE(SUM([YourMeasure20]))),
0,
1
)

Go to the matrix visual and add the MeasureVisibility measure to the visual-level filters pane.
Set the filter to show only rows where MeasureVisibility is 1.

[CBO2404]

This will give an error on SUM([YourMeasure1]): Parameter is not the correct type.

[v-nmadadi-msft]

Hi @CBO2404 ,

Please modify the DAX measure like this and then try to filter at visual level

MeasureVisibility = 
IF(
ISBLANK(CALCULATE(Table[Measure1])) &&
ISBLANK(CALCULATE(Table[Measure2])),
... &&
ISBLANK(CALCULATE(SUM([YourMeasure20]))),
0,
1

Here is the screenshot of it working for my sample data

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank you