Calculate occurence based on multiple colomns filters

[Anonymous]

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_ 

[FreemanZ]

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:

[Anonymous]

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 

[FreemanZ]

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:

[Anonymous]

Can you please share the pbix if possible?  @FreemanZ 

[FreemanZ · Accepted solution]

hi @Anonymous 

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

[Anonymous]

Its working, thanks buddy!