Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count unique/distinct values from two different tables

I have two columns in two different tables. 1) Accounts[Account Name] 2) Visits[Regarding Account]   I want to count how many unique/distinct values from Visits[Regarding Account] are on Accounts...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  Anonymous ,

     

    Using below dax expression:

     

    Measure = CALCULATE(DISTINCTCOUNT(Visits[Regarding Account]),ALL(Visits))-CALCULATE(DISTINCTCOUNT(Visits[Regarding Account]),FILTER(ALL(Visits),Visits[Regarding Account] in FILTERS(Accounts[Account Name])))

     

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

     

robinHH's avatar
robinHH
Frequent Visitor
5 years ago

Hi!


if the tables have no relationship you can use the treatas function within an calculate function. 

The treatas function filters the visitor function from my example by the values from the account name table. Afterwards, only the distinct values are calculated by the measure.

 

I hope I was able to help you 🙂

Best wishes
Rob

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Rob! robinHH 
    Thanks for your contribution. 
    Unfortunately I receive below error. As I see it we also need to select column for the "account" table. Currently it's just referring to the table but does not point to column 🙂

     

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      5 years ago

      Hi  Anonymous ,

       

      Using below dax expression:

       

      Measure = CALCULATE(DISTINCTCOUNT(Visits[Regarding Account]),ALL(Visits))-CALCULATE(DISTINCTCOUNT(Visits[Regarding Account]),FILTER(ALL(Visits),Visits[Regarding Account] in FILTERS(Accounts[Account Name])))

       

      And you will see:

      For the related .pbix file,pls see attached.

       

      Best Regards,
      Kelly

      Did I answer your question? Mark my post as a solution!