topic Re: Optimized way to get Distinct count metric in DAX in DAX Commands and Tips

Optimized way to get Distinct count metric in DAX

abhiram342 — Thu, 03 Sep 2020 01:57:28 GMT

Hi All,

I have two Fact Tables Table A and Table B. I want to get distinct count metric from Column which is comoon from both tables. Currenlty, I'm using below code and it's taking Time. Please Suggest optimized way for calculating it

Example:

FactA[Column] & FactB[Column]:

DistinctCount = DISTINCTCOUNT('FactA'[Column])+DISTINCTCOUNT('FactB'[Column])-CALCULATE(DISTINCTCOUNT('FactA'[Column]),TREATAS(VALUES('FactB'[Column]),'FactA'[Column]))

Thanks,

Abhiram

Re: Optimized way to get Distinct count metric in DAX

Greg_Deckler — Thu, 03 Sep 2020 02:12:59 GMT

abhiram342 - Be easier to verify if had sample data or data set but maybe try:

DistinctCount = COUNTROWS( DISTINCT( UNION( SELECTCOLUMNS('FactA',"Column",[Column]), SELECTCOLUMNS('FactB',"Column",[Column]) ) ) )

Re: Optimized way to get Distinct count metric in DAX

amitchandak — Thu, 03 Sep 2020 02:32:56 GMT

abhiram342 , Try like

countx(distinct(union(all('FactA'[Column]),all('FactB'[Column]))),[Column])

countrows(distinct(union(all('FactA'[Column]),all('FactB'[Column]))))

Re: Optimized way to get Distinct count metric in DAX

Anonymous — Thu, 03 Sep 2020 10:51:01 GMT

[Distinct Count] = COUNTROWS( DISTINCT( UNION( VALUES( FactA[Column] ), VALUES( FactB[Column] ) ) ) )