Distinct Count of USERID by Date

[Milan14]

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

[Jihwan_Kim]

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

)

)

[amitchandak]

@Milan14 ,

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