how to use a measure in a slicer

[BarbaraYanChen]

Hello,

My table has a measure 25% MoM Flag = IF([MoM Delta %] >= 0.25, 1, 0). I want to make a slicer to show 0, 1, select all using this measure.  

I created a new table called "MoM Flag Ind", that has only one column "Flag_Ind' and two rows (1 and 0).  But I don't know how to connect the table to measure to make slicer work.

Can anyone help?  thanks.

Barbara

[danextian · Accepted solution]

Hi @BarbaraYanChen 

Meaures don't have a row context and the values are evaluated based on othe other dimensions added together with it to a visual. There are techniques to make them appear like they can be used as dimensions but the actual approach depends on your use case. In the images below, a disconnected table with a column of values 0 and 1 is created and used in a slicer. The slicer selection is then compared with Flag measure in the table visual in a measure called  Flag Filter. The latter is used as a visual filter.

Flag Filter = 
IF ( [Flag] IN VALUES ( _Flag ), 1, 0 )

The Flag and Flag Filters are evaluated for each combination of Category and Geo columns so this won't work if used in a visual like a card which doesn't allow for such evluation to happen.

Please see the attached sample  pbix.

If this isn't what you're looking for, please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post  a link to an Excel file or a sanitized copy your pbix stored in the cloud.

[danextian · Accepted solution]

Hi @BarbaraYanChen 

Meaures don't have a row context and the values are evaluated based on othe other dimensions added together with it to a visual. There are techniques to make them appear like they can be used as dimensions but the actual approach depends on your use case. In the images below, a disconnected table with a column of values 0 and 1 is created and used in a slicer. The slicer selection is then compared with Flag measure in the table visual in a measure called  Flag Filter. The latter is used as a visual filter.

Flag Filter = 
IF ( [Flag] IN VALUES ( _Flag ), 1, 0 )

The Flag and Flag Filters are evaluated for each combination of Category and Geo columns so this won't work if used in a visual like a card which doesn't allow for such evluation to happen.

Please see the attached sample  pbix.

If this isn't what you're looking for, please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post  a link to an Excel file or a sanitized copy your pbix stored in the cloud.

[BarbaraYanChen]

Hello Danextian,

I have made a slicer (25% MoM Flag) using your solution.  Now, can I have another question: to show Yes/No on the slicer instead of 1/0.

Thanks,

Barbara

[BarbaraYanChen · Accepted solution]

Hi Danextian,

Thanks so much for your solution.  I looked at your powerbi example, and copied your steps, it worked well.

Thanks so much for your help!

Barbara

[Ashish_Mathur]

Hi,

I'd like to try.  Share the download link of the PBI file.

[Deku]

If you are just trying to filter the table you could add a check to your [mom delta %] 

If( [25% mom flag] >= 0.25, [mom delta %] )

This returns blank if greater than 25% and the values will not show in the visual

You could also add the 25% measure to the filter pane on the visual filter and condition where result = 1.

Or do you want end users to be able to select from a slicer?