Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ViktorJ
Frequent Visitor

Filtering a measure in DAX

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 🙂

5 REPLIES 5
ValtteriN
Super User
Super User

Hi,

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

ValtteriN_0-1674645325174.pngValtteriN_1-1674645351265.png

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

ValtteriN_2-1674645714577.png


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:
ValtteriN_3-1674645893233.png

 

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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 :-). 

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

  



ValtteriN_2-1674650258528.png

ValtteriN_0-1674649990961.png




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




FreemanZ
Super User
Super User

hi @ViktorJ ,

 

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

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 :-). 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors