Forum Discussion

Kosh's avatar
Kosh
Frequent Visitor
1 year ago
Solved

DAX function to apply OR condition on multiple columns based on Slicers

Hi,

 

I have multiple slicers (Location, Gender, Age, etc). I want to count all rows for any value which matches with the slicer selection.

Example - If user selected Location=US, UK + Gender=Female + Age=18-24 Years, the output should be anyone who is from Locations = US or UK, OR Gender is Female OR Age is 18-24 Years.

 

I tried below formula but not working.

SelectedEmployees =
CALCULATE(
    COUNTROWS(tblData),
    FILTER(
        'tblData',
        (tblData[Location] in VALUES(tbLocations[Locations]) ||
        tblData[Gender] in VALUES(tblGender[Gender]) ||
        tblData[Age] in VALUES(tblAge[AgeGroups])) 
    )
)
"DAX"
"OR"
"Slicers"
  • Deku's avatar
    Deku
    1 year ago

    Need a all() to remove the current filter context

     

    SelectedEmployees =

    CALCULATE(

        COUNTROWS(tblData),

        FILTER(

           All( 'tblData' ),

            (tblData[Location] in VALUES(tbLocations[Locations]) ||

            tblData[Gender] in VALUES(tblGender[Gender]) ||

            tblData[Age] in VALUES(tblAge[AgeGroups])) 

        )

    )

  • ThxAlot's avatar
    ThxAlot
    1 year ago

    Make sure these 3 tables tbLocations, tblGender, tblAge DON'T filter each other. If so, the measure is fairly simple,

  • sjoerdvn's avatar
    sjoerdvn
    1 year ago
    SelectedEmployees = 
CALCULATE(
    COUNTROWS(tblData),
        ALL('tbLocations'),ALL(tblGender), ALL(tblAge),
        OR(OR(tblData[Location] in VALUES(tbLocations[Locations]),
                tblData[Gender] in VALUES(tblGender[Gender])), 
            tblData[Age] in VALUES(tblAge[AgeGroups]))   
)

7 Replies

  • Deku's avatar
    Deku
    Super User
    1 year ago

    Need a all() to remove the current filter context

     

    SelectedEmployees =

    CALCULATE(

        COUNTROWS(tblData),

        FILTER(

           All( 'tblData' ),

            (tblData[Location] in VALUES(tbLocations[Locations]) ||

            tblData[Gender] in VALUES(tblGender[Gender]) ||

            tblData[Age] in VALUES(tblAge[AgeGroups])) 

        )

    )

    • Kosh's avatar
      Kosh
      Frequent Visitor
      1 year ago

      Thanks for the reply. How to show this data in a table/matrix with breadkdown by any field? Using ALL in filter is giving same value for all rows table/matrix.

      • sjoerdvn's avatar
        sjoerdvn
        Solution Sage
        1 year ago
        SelectedEmployees = 
CALCULATE(
    COUNTROWS(tblData),
        ALL('tbLocations'),ALL(tblGender), ALL(tblAge),
        OR(OR(tblData[Location] in VALUES(tbLocations[Locations]),
                tblData[Gender] in VALUES(tblGender[Gender])), 
            tblData[Age] in VALUES(tblAge[AgeGroups]))   
)
  • ThxAlot's avatar
    ThxAlot
    Super User
    1 year ago

    Make sure these 3 tables tbLocations, tblGender, tblAge DON'T filter each other. If so, the measure is fairly simple,

  • v-kathullac's avatar
    v-kathullac
    Community Support
    1 year ago

    Hi Kosh  ,

     

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
    If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.


    Regards,

    Chaithanya.

  • v-kathullac's avatar
    v-kathullac
    Community Support
    1 year ago

    Hi @Kosh  ,

     

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
    If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.


    Regards,

    Chaithanya.