Lookup Values and Measures

[Anonymous]

Hi, i have 2 columns one that has a customer ID and another that has if they are OnPrem or Cloud. What i am looking to do is do a look to say if they have both then output upgraded. Something along the lines of IF Customer ID has Cloud and On Prem then output Upgraded. any help will be amazing

[v-shex-msft]

Hi @Anonymous,

You can try to use below measure if it works for your scenario:

Result =
VAR currCustomer =
    SELECTEDVALUE ( Table[Customer ID] )
VAR itemcount =
    CALCULATE (
        COUNTROWS ( VALUE ( Table[Type] ) ),
        FILTER (
            ALLSELECTED ( Table ),
            Table[Customer ID] = currCustomer
                && Table[Type] IN { "OnPrem", "Cloud" }
        )
    )
RETURN
    IF ( itemcount = 2, "upgraded", "normal" )

Regards,
Xiaoxin Sheng