Count unique/distinct values from two different tables

[Anonymous]

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

[robinHH]

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]

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 · Accepted solution]

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!

[Anonymous]

@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 Stege	Eskebjerg-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 Roskilde	2B VVS Teknik Aps 31274753 2620 Albertslund
Brødrene A. & O. Johansen A/S 4300 Holbæk	Skærbæk VVS 25326598 4780 Stege

[amitchandak]

@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])