Forum Discussion

bml123's avatar
bml123
Post Patron
4 years ago
Solved

Distinct count by category

Hi,

 

I have a Clients table with client details

Clients IdNameDOBCountry
1  Uk
2  USA
3  UK

 

and another products table 

Product IdProduct TypeClient Id
100P11
101P21
102P12

 

I want to get distinct country count by product type

Product TypeCount of distinct country
P12
P21

 

How do I achieve that?

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    4 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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