Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Distinct Count

I am new to Power BI and I would really appreciate your help

 

I have a 2 different data sets. I need to do a distinct count: 

So I need to do record count of distinct referrals and distinct warrants by anonymized community name. I am not sure how to go about doing this. Referral ID and Annonymized community name are in 1 data set and warrant id is in another data set but I put it together in the report section. 

 

I would really appreciate any help on this. It looks simple but I am not sure how to do it since i have not used Power BI before. 

 

Thank you 

 

Count from summarised row count 

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi  Anonymous 

    Thanks for reaching out to us.

    Is there any same key in both datasets? Use it to find related value in another dataset with function filter(). 

    You can try code like this, 

    distinct referrals = CALCULATE(DISTINCTCOUNT(Table1[Referral ID]),ALLEXCEPT(Table1,Table1[Anonymized Community Number]))
    distinct warrants = CALCULATE(DISTINCTCOUNT(Table2[Warrant ID]),ALLEXCEPT(Table1,Table1[Anonymized Community Number]))
    -
    In my sample, there is a one-to-one relationship between two tables

    reuslt

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi  Anonymous 

    Thanks for reaching out to us.

    Is there any same key in both datasets? Use it to find related value in another dataset with function filter(). 

    You can try code like this, 

    distinct referrals = CALCULATE(DISTINCTCOUNT(Table1[Referral ID]),ALLEXCEPT(Table1,Table1[Anonymized Community Number]))
    distinct warrants = CALCULATE(DISTINCTCOUNT(Table2[Warrant ID]),ALLEXCEPT(Table1,Table1[Anonymized Community Number]))
    -
    In my sample, there is a one-to-one relationship between two tables

    reuslt

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.