Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
In the following dataset, I created a measure that counts distinct clients where contact type <> "EM" || val1 <> 'Y'|| val2<>"Y".
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
Solved! Go to 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
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
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
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
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
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
@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 :
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
77 | |
68 | |
54 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |