Count distinct product if contains condition1 and condition2

[wolfie777]

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 🙂

[Pragati11]

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

[wolfie777]

Thanks @Pragati11, I tried your solution but it didn't work. It needs to be distinct count of product not criteria.

[zzcc]

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:

[parry2k · Accepted 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.⚡

[wolfie777]

Thank you so much @parry2k it works! 

[selimovd]

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?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[wolfie777]

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.