Optimized way to get Distinct count metric in DAX

[abhiram342]

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

[Greg_Deckler · Accepted solution]

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

[amitchandak · Accepted solution]

@abhiram342 , Try like

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

or

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

[Anonymous · Accepted solution]

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

[amitchandak · Accepted solution]

@abhiram342 , Try like

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

or

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

[Greg_Deckler · Accepted solution]

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