Measure result based on OR conditions

[Anonymous]

Hi Community,

In the following dataset, I created a measure that counts distinct clients where contact type <> "EM" || val1 <> 'Y'|| val2<>"Y".

Measure =

CALCULATE (

DISTINCTCOUNT ('Table'[Client] ),

'Table'[Contact Type] <> "EM"

|| 'Table'[Val1] = "N"

|| 'Table'[Val2] = "N"

)

But the measure reutns 9 clients, I expect 6 (higlighted in yellow). 

The logic is to return the clients who have not provided EM contact type OR not provided Val1 Or Not provided Val2.

My PBI file:  https://1drv.ms/u/s!Ag919_pO_UKrgThqrNGx2Dwbp9Q6?e=YjOLvi

Any help is appreciated. Thanks

[TomMartens · Accepted solution]

Hey @Anonymous ,

the measure is a little more complex:

Measure 2 = 
var t_not_EM = 
    CALCULATETABLE(
        VALUES( 'Table'[Client] )
        ,'Table'[Contact Type] <> "EM"
    )
var t_EM = 
    CALCULATETABLE(
        VALUES( 'Table'[Client] )
        ,'Table'[Contact Type] = "EM"
    )
var notEM =
    EXCEPT(
        t_not_EM
        , t_EM
    )
var t_VAL1_VAL2 = 
    EXCEPT(
        CALCULATETABLE(
            VALUES( 'Table'[Client] )
            ,'Table'[Val1] = "N" || 'Table'[Val2] = "N"
        )
        , notEM
    )
return
    COUNTROWS(
        UNION(
            notEM
            , t_VAL1_VAL2
        )
    )

Based on the measure you get this result on a card visual:

If you use CONCATENATEX instead of COUNTROWS you will get this on the card visual:

...
CONCATENATEX(
        UNION(
            notEM
            , t_VAL1_VAL2
        )
        , 'Table'[Client]
        , " | "
    )

The card visual:

Hopefully, this provides what you are looking for.

Regards,
Tom

[darshaningale]

Please explain why you dont want the below rows ? 

If you create a new column ("valid" below) with your filter criteria , it shows 9 clients.

Regards

DI

[Samarth_18]

Hi @Anonymous ,

The reason why you are getting 9 is you have added OR condition in type and val1,2 so its considering all type which is not EM in the count. Based on condition you are getting correct output so kindly revisit the condition. 🙂

Thanks,

Samarth

[VIJAYKUMART]

Hi.. can u please specify data u have and data output that satiffies condition in excel by using filters in excel..  the output logic is not clear

[Anonymous]

@VIJAYKUMART  The output logic is to return the clients who have not provided EM contact type OR not provided Val1 Or Not provided Val2. As you see in the dataset a client can have more than one Contact type  like H,O,EM. This is the difficult part of the logic.

for example :