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[Account Name].

 

Thx

Thomas

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!

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    If they are joined you can use distinctcount( Visits[Regarding Account])

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    amitchandak , yes - please have a look below.
    In this example the result should be 1.
    Skærbæk VVS 25326598 4780 Stege is there two times but Visits[Regarding Account] should be unique/distinct values and then look how many values are on Accounts[Account Name]

     

    Visits[Regarding Account]Accounts[Account Name]
    Skærbæk VVS 25326598 4780 StegeEskebjerg-Mørkøv VVS 4440 Mørkøv
    Skærbæk VVS 25326598 4780 Stege​Bent VVS 2300 København S
    Sanistål A/S Centrallager Billund 7190 Billund​NITO-TEK A/S - ( Skovhavens VVS A/S ) Skovhavens VVS A/S 5270 Odense N
    Brødrene A. & O. Johansen A/S 4000 Roskilde2B VVS Teknik Aps 31274753 2620 Albertslund
    Brødrene A. & O. Johansen A/S 4300 HolbækSkærbæk VVS 25326598 4780 Stege
  • 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
        Icon for Community Support rankCommunity 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!