topic Re: Distinct Count of USERID by Date in DAX Commands and Tips

Distinct Count of USERID by Date

Milan14 — Thu, 21 Oct 2021 08:21:49 GMT

Hi All,

I would like to calculate the distinct count of USERID by DATE and CUSTOMER TYPE for Year-to-Date (1/1/2021 – 5/1/2021).

Sample table:

Expected outcome in the table:

Expected outcome in matrix table:

Any help would be much appreciated.

Thanks in advance!

Kindly Regards,

Milan

amitchandak — Thu, 21 Oct 2021 17:02:38 GMT

Try a measure like

calculate(sumx(summarize(Table, Table[Date], Table[Customer Type], "_1", distinctcount(Table[UserID]) ), [_1]), datesytd('Date'[Date]))

datesytd , in case you need ytd

Jihwan_Kim — Thu, 21 Oct 2021 17:02:45 GMT

Hi,

Please check the below picture and the attached pbix file.

Distinct count userid : =

IF (

HASONEVALUE ( Data[type] ),

COUNTROWS ( DISTINCT ( DISTINCT ( Data[userid] ) ) ),

SUMX (

ALL ( Data[date], Data[type] ),

CALCULATE ( COUNTROWS ( DISTINCT ( Data[userid] ) ) )

)

Distinct count userid by type only: =

IF (

HASONEVALUE ( Data[type] ),

SUMX (

ALL ( Data[date] ),

CALCULATE ( COUNTROWS ( DISTINCT ( Data[userid] ) ) )

SUMX (

ALL ( Data[date], Data[type] ),

CALCULATE ( COUNTROWS ( DISTINCT ( Data[userid] ) ) )

)