Filter a matrix on a measure under a specific category/column only

[MBZA]

I have a matrix visual that looks something like this:

The measures are defined so that Measure 1 is BLANK() for category 2 and category 3, and vice versa.

I would like to filter the matrix so that only rows where Measure 1 = 1 are shown. This is easy to do if I remove the categories, but when the categories are added the Measure 1 card in the filter pane gets disabled, which makes sense given that it doesn't which version of Measure 1 to filter on.

I know one approach would be to create separate measures Measure2_Category2, Measure2_Category3 etc. and remove the categories field from the columns, but there are actually a lot more measures and categories so this feels impractical.

Are there any other workarounds to filter based on measures when there are selections in the columns field well? It's easy to do in Analyse in Excel.

Related question: is there any way to hide the columns where the measures are defined to be blank? ie have a final result like this:

This happens automatically with Analyse in Excel.

[Anonymous]

Hi @MBZA ,

You need to create an entry table that looks like this:

Then apply it to the matrix's column value:

And then please create a new measure:

Measure = 
VAR __curr_col = SELECTEDVALUE('ColumnNameTable'[ColumnName])
VAR __result = 
 SWITCH(
    __curr_col,
    "Measure 1", [Measure 1],
    "Measure 2", [Measure 2],
    "Measure 3", [Measure 3]
 )
RETURN
    __result

Finally apply this filter, and the output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[MBZA]

Thanks for the reply. I didn't think about how it would affect behaviour enough, but rather than categories it's really a set of calculation group items. Is there still a way to make it work?

[hnguy71]

Hi @MBZA ,
you can click on the visual and then add a filter to your measure 1 with the condition that the output of the value is 1:

[MBZA]

Hi, thanks for the reply. It looks like this works (and seems to do it based on whatever is in the Total column for that measure), but for simplicity I left out the fact in my question that the columns are actually a calculation group. I didn't realise that this would cause different behaviour, but using the calculation group the filter pane item is disabled.

[hnguy71]

Hi @MBZA,
Since you're using a calculation group to return your set of measures, you'll need to create a NEW measure to evaluate if [Measure 01] returns 1 or 0. Then you would filter the visual for 1. 

Btw, the measure filter actually would filter at the context intersection and rather than at the total line. The total line only returns the max of the value, in this case 1. You can turn off the total line and still have it filtered by 1.  

[MBZA]

Hi @hnguy, I don't really follow.

Measure 1 is already the filter criteria. I can create a new measure as Measure New = [Measure 1], but that is still disabled in the filter pane.

[hnguy71]

Hi @MBZA ,
Can you post a screenshot of the filter being disabled? 

[MBZA]

@hnguy71I tried to upload a GIF but it gave an error:

When I add the calculation group to the columns then I can't click on any of the items in the filter above. For example instead of getting the dropdown nothing happens when I click on "is greater than or equal to" and I can't change 1 to something else.

[hnguy71]

Hi @MBZA ,

You can't add the calculation group... it must be a measure. Can you create a new measure that gives you the exact same output as your [measure 01] ?

[MBZA]

@hnguy71I'm not sure what you mean. The filter I showed is on the measure, not the calculation group itself.

The matrix looks like this:

ie the data setup is:

I have added Measure 1 to the filter pane, and am trying to filter Measure 1 to be equal to 1. The filter for measure 1 is disabled.

[hnguy71]

Hi @MBZA 

Not sure where the confusion lies but you should definitely be able to use a measure and add it to your visual to filter for it:

I have attached a sample pbix for your reference. Just make sure you filter at the VISUAL level.

Hope that helps!