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.
Hi all. I am trying to work out how to distinct count products if they match the criteria as per below:
My measure returns count 3 but it should be 2. I need to count only products which match both criteria. Any help will be much appreciated! Thank you 🙂
Solved! Go to Solution.
@wolfie777 try this measure
Count =
SUMX (
SUMMARIZE (
'Product',
'Product'[Product],
"@Count",
COUNTROWS (
CALCULATETABLE (
VALUES ( 'Product'[Criteria] ),
'Product'[Criteria] IN { "A", "B" }
)
)
),
IF ( [@Count] = 2, 1 )
)
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
HI @wolfie777 ,
You can try creating the following measure:
flag3 =
CALCULATE
(
DISTINCTCOUNT('Table (5)'[Criteria]),
FILTER(
ALL('Table (5)'), 'Table (5)'[Product] <= MAX('Table (5)'[Product]) && 'Table (5)'[Criteria] IN {"A", "B"}
)
)
Now move this measure to the card visual and you will get the required output.
Thanks,
Pragati
Thanks @Pragati11, I tried your solution but it didn't work. It needs to be distinct count of product not criteria.
HI, @wolfie777
try to create 2 measures like this:
isContain =
var _table=FILTER(ALL('Table'),[Product]=MAX('Table'[Product]))
var _product=SUMMARIZE(_table,[Criteria])
var _if=IF(("A"in _product)&&("B"in _product),1)
return _if
count = CALCULATE(DISTINCTCOUNT('Table'[Product]),FILTER(ALL('Table'),[isContain]=1))
result:
@wolfie777 try this measure
Count =
SUMX (
SUMMARIZE (
'Product',
'Product'[Product],
"@Count",
COUNTROWS (
CALCULATETABLE (
VALUES ( 'Product'[Criteria] ),
'Product'[Criteria] IN { "A", "B" }
)
)
),
IF ( [@Count] = 2, 1 )
)
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hey @wolfie777 ,
I think the 3 are correct. You filter for Criteria A or B. When you filter that table there are left:
Product 1
Product 2
Product 5
Then the distinct count is 3.
Did you want to calculate something different?
Thank you @selimovd. I need to achieve result 2 so need to adjust my measure. It should count distinct products only if they cointain both criteria A and B. I tried many different ways but nothing worked.
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 |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |