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

[Kosh]

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

    )

)

[Deku · Accepted solution]

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 · Accepted solution]

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

[v-kathullac]

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]

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.

[ThxAlot · Accepted solution]

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

[Deku · Accepted solution]

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]

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 · Accepted solution]

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]

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