Distinct count by category

[bml123]

Hi,

I have a Clients table with client details

Clients Id	Name	DOB	Country
1			Uk
2			USA
3			UK

and another products table 

Product Id	Product Type	Client Id
100	P1	1
101	P2	1
102	P1	2

I want to get distinct country count by product type

Product Type	Count of distinct country
P1	2
P2	1

How do I achieve that?

[Anonymous · Accepted solution]

Hi @bml123 ,

Please check this measure.

Measure =
VAR ids =
    CALCULATETABLE (
        VALUES ( products[Client Id] ),
        FILTER (
            ALLSELECTED ( products ),
            products[Product Type] = SELECTEDVALUE ( products[Product Type] )
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Clients[Country] ),
        FILTER ( ALLSELECTED ( Clients ), Clients[Clients Id] IN ids )
    )

Best Regards,

Jay

[PhilipTreacy]

Hi @bml123 

Download example PBIX file

Try this

Distinct Country Count by Product Type = CALCULATE(DISTINCTCOUNT([Client Id]), FILTER('Data', 'Data'[Product Type] = SELECTEDVALUE('Data'[Product Type])))

regards

Phil

[jsaunders_zero9]

Hi @bml123 

When you say table do you need this represented in a visual or data table?

Thank you