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=Fe...
"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]))   
)
Deku's avatar
Deku
Icon for Super User rankSuper 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
    Icon for Solution Sage rankSolution 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]))   
)
    • Kosh's avatar
      Kosh
      Frequent Visitor
      10 months ago

      Thanks for your reply. Not sure where the issue is but the formula is not giving correct output. Example - When I select Male in Gender filter the count is 1000+, so if I select any other filter the value should either remain same or increase (as using OR filter) but the count is going down.

      CALCULATE(
      COUNTROWS(tblDnI), ALL(tblGender),ALL(tblSexualOrientation),ALL(tblReligion),ALL(tblDisability),all(tblOccupation),all(tblEthnicity),

      (tblDnI[Gender] in VALUES(tblGender[Gender])
      ||
      tblDnI[Sexual Orientation] in VALUES(tblSexualOrientation[Sexual Orientation])
      ||
      tblDnI[Religion] in VALUES(tblReligion[Religion])
      ||
      tblDnI[Disabled] in VALUES(tblDisability[Disabled])
      ||
      tblDnI[Household Occupation] in VALUES(tblOccupation[SEB])
      ||
      tblDnI[Ethnic Origin] in VALUES(tblEthnicity[Ethnicity])
      ))