topic Re: Calculate occurence based on multiple colomns filters in DAX Commands and Tips

Calculate occurence based on multiple colomns filters

Anonymous — Mon, 24 Apr 2023 11:43:51 GMT

Hi Team

I have below dataset:

City	Value1	Value2
Delhi	Yes
Delhi	No	Na
Delhi	No	Yes
Delhi	NA	No
Mumbai	Yes	No
Mumbai	Yes	No
Mumbai	No	NA
Mumbai	No	NA
Bangalore	Yes	na
Bangalore	no	na
Bangalore	No
Bangalore	no	Yes

I want to return below output in a card visual using measure :

No of locations where Value1 and value2 is Yes , output would be 2 here

Delhi and Bangalore has Value1 and Value2 Yes where mumbai has Value 1 yes but no rows has value2 as yes.

Greg_Deckler tamerj1 johnt75 Jihwan_Kim Wilson_

Re: Calculate occurence based on multiple colomns filters

FreemanZ — Mon, 24 Apr 2023 11:56:19 GMT

hi Anonymous

try to plot a card visual with a measure like:

Measure = VAR _table = ADDCOLUMNS( VALUES(Data[City]), "Value1", CALCULATE(MAX(Data[Value1])), "Value2", CALCULATE(MAX(Data[Value2])) ) RETURN COUNTROWS( FILTER( _table, [Value1]="Yes"&&[Value2]="Yes" ) )

it worked like:

Re: Calculate occurence based on multiple colomns filters

Anonymous — Mon, 24 Apr 2023 12:09:25 GMT

Thank you for the promt response unfortunately its not working. I just gave dummy data but Value1 and value2 has other data apart from NA, No so Max fucntion is capturing those values instead Yes.

how to explicit call Yes as Max fucntion giving some other output

FreemanZ

Re: Calculate occurence based on multiple colomns filters

FreemanZ — Mon, 24 Apr 2023 12:16:42 GMT

hi Anonymous

then try like:

Measure = VAR _table = GENERATE( VALUES(Data[City]), CALCULATETABLE( CROSSJOIN(VALUES(Data[Value1]), VALUES(Data[Value2])) ) ) RETURN COUNTROWS( FILTER( _table, [Value1]="Yes"&&[Value2]="Yes" ) )

it worked like:

Re: Calculate occurence based on multiple colomns filters

Anonymous — Mon, 24 Apr 2023 12:28:56 GMT

Can you please share the pbix if possible? FreemanZ

Re: Calculate occurence based on multiple colomns filters

FreemanZ — Mon, 24 Apr 2023 13:11:37 GMT

hi Anonymous

the data is exactly from you and i only name it as data. all others are included in the previous replies.

Re: Calculate occurence based on multiple colomns filters

Anonymous — Mon, 24 Apr 2023 13:21:26 GMT

Its working, thanks buddy!