Filtering a measure in DAX

[ViktorJ]

I have a challenge filtering a measure. I know I can't use a slicer, and I need the filtering to happen in the whole dashboard. Therefore, I need a DAX to filter out all the values that are not above zero. Example below:

I have a table called HotelsInGermany. In this table, there is a measure called [# of hotels]. In this measure, there are in some cases zero hotels in some of the cities. I intend to make another DAX on the [# of hotels], where I filter for all values above zero and then use this measure instead of the original one.

Question: How do I filter out all the zeros in the measure [# of hotels] so I only can see the cities with at least one hotel via a DAX measure?

I hope you guys can help me 🙂

[ValtteriN]

Hi,

The most straight-forward way is to use slicer pane:

Additionally you can create dynamic filtering using many different methods. E.g. using a filter measure and an iput table:

Dax:

Dynamic filter =

var selection = MAX('Table (19)'[Column1]) return

SWITCH(selection,

"Above 0",IF([Measure 35]>0,1,0),

"Below 0",IF([Measure 35]<0,1,0))

End result:
By selecting different values in the new slicer the filter logic of the table changes.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[ViktorJ]

hi @ValtteriN ,
I need to use this measure in various visuals, which means the filtering has to be in a DAX. I think making a dynamic filter in the way you are thinking would make me have to add all the visuals to the filter. My idea was to sort out all the zeros in a new DAX and use this DAX instead of the other one.

But thank you for sharing your idea. Let me know if you have any new ideas :-). 

[ValtteriN]

Hi,

If you want a to apply filter logic to all your visuals you can create a calculation group referencing the filter measure:

Global 0 filter = IF([Measure 35]>0,1,0)



calculate(selectedmeasure(),FILTER('Table (18)',[global 0 filter] = 1))


Now we can apply this to all pages and it will work without applying it to a visual:
 

You can also use the filtering logic within a calculation group to apply it to multiple measures.

calculate(selectedmeasure(),FILTER('Table (18)',[Measure 35]>0))

[FreemanZ]

hi @ViktorJ ,

You may try to put the measure [# of hotels] to the filter pane and choose >0

[ViktorJ]

hi @FreemanZ,
I need to use this measure in various visuals, which means the filtering has to be in a DAX. I think adding it to the filter pane would make me filter each visual every time I use the measure [# of hotels].

But thank you for sharing your idea. Let me know if you have any new ideas :-).