DAX MEASURE to get COUNTs based on OR Logic on 2 columns

[revansh]

Hi All,

I am trying to add a DAX measure with OR logic .

LOC | INC | ASSIGNED_TO

NY  INC1 SALES_TEAM 

NY  INC2  SALES_GROUP

NY  INC3  HR_DEPT

NA INC4   SALES_TEAM

KY  INC5   HR_DEPT

NA  INC      HR_GROUP

Output:  I want to get the count of incidents where LOC =NY and ASIIGNED_to Contains "SALES".

If i apply the logic on above dataset, i should get the count as 4 (INC1,INC2,INC3,INC4).

Thanks

[Anonymous]

@revansh  - 

Try this Measure:

Count Measure = 
COUNTROWS (
    FILTER (
        ALL ( Table1 ),
        OR ( 
            Table1[LOC] = "NY", 
            FIND ( "SALES", Table1[ASSIGNED_TO], 1, 0 ) > 0 
        )
    )
)

Cheers!

Nathan

[revansh]

Hi NatelPeterson,

Thanks for the dax statement. it is working as expected. 

Now, instaed of hardcoding the values in DAX, can i add slicers on the report and pass slicer selected values to DAx Statement.

Thanks

[hthota]

Hi,

Create the measure as given below measure.

Count Measure =

COUNTROWS (

FILTER (

ALL ( Sheet1 ),

OR (

Sheet1[LOC] = SELECTEDVALUE(Sheet1[LOC]),

FIND ( SELECTEDVALUE(Sheet1[ASSIGNED_TO]), Sheet1[ASSIGNED_TO], 1, 0 ) > 0

)

)

)

It would help you.

Thanks,

Hemanth Thota.

[Anonymous]

Try something like this. You may need a disconnected table with various search strings. Also, I'm not sure whether you'd want the max, or only return a value if a single item has been selected. IF the latter, you could check for HASONEVALUE, and return BLANK if that is false for either of the parameters.

Count Measure = 
var loc_filter = MAX(Table1[LOC])
var assigned_filter = MAX(<Disconnected Slicer Table>)
COUNTROWS (
    FILTER (
        ALL ( Table1 ),
        OR ( 
            Table1[LOC] = loc_filter, 
            FIND ( "SALES", assigned_filter, 1, 0 ) > 0 
        )
    )
)

Cheers,

Nathan