Distinct Count of Reference IDs in each Group

[julesdude]

Hi,

I have a table like this:
Leases

Asset Ref	Tenant
A	Rob
A	Steve
A	Jan
B	Ian
B	Jane
C	Jane
C	Jane
C	Mike
D

I want to get a distinct count of Tenants for each Asset Ref based on the following criteria:

- If a tenant appears twice or more in the same asset, it is only counted once

- If a tenant appears again in a different asset or others, these can be counted but only once per asset

- Assets with no Tenant listed against them are not counted (blank)

I need this to be appear as following:

Asset Ref	Tenants
A	3
B	2
C	2
D	0

Lastly, I'd need a measure that will total all these to put the grand total in Card visual.

I've been trying out the following so far, but they have not worked for me:

Total Tenants = 
CALCULATE(DISTINCTCOUNT(Leases[Tenant]), GROUPBY(Leases, Leases[Asset Reference]))

[DAXDigger · Accepted solution]

Measure = 
VAR _table = FILTER(SUMMARIZECOLUMNS('Table'[Asset Ref    ],'Table'[Tenant]),'Table'[Tenant]<>BLANK())
RETURN COUNTROWS(_table)

[julesdude]

Thanks and it worked!