DAX to calculate number of distinct values in 2 columns in 2 tables - SSAS

[adam12237]

I have a need for a single DAX expression in SSAS that would allow me to calculate the number of distinct memberIDs found within a Calls table and a Faxes table. I am using a tabular model in SSAS so I am unable to create any calculated columns. Any guidance is appreciated.

Thanks,

Adam

[v-lionel-msft · Accepted solution]

Hi @adam12237 ,

If you connect to SQL Server Analysis Services by using Live Connection, you can't model in Power BI Desktop.

Because in Live Connection mode, Power BI will connect the entire external model. Once connected, it can not be modified in Power BI, which means that you can only model the data in SSAS.

So for your problem, you have the following two solutions:

1. Calculate what you want in the data source .

2. Use Import mode.

Best regards,
Lionel Chen

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

[Greg_Deckler]

So I think perhaps something like:

Measure =
  VAR __Calls = 'Calls'
  VAR __Faxes = 'Faxes'
  VAR __Table = 
    UNION(
      SELECTCOLUMNS(__Calls,"__MemberID",[MemberID]),
      SELECTCOLUMNS(__Faxes,"__MemberID",[MemberID])
    )
RETURN
  COUNTROWS(
    DISTINCT(
      __Table
    )
  )