Forum Discussion

ViktorJ's avatar
ViktorJ
Frequent Visitor
3 years ago

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

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi ViktorJ ,

     

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

    • ViktorJ's avatar
      ViktorJ
      Frequent Visitor
      3 years ago

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

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      ViktorJ
      Frequent Visitor
      3 years ago

      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's avatar
        ValtteriN
        Icon for Community Champion rankCommunity Champion
        3 years ago

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