topic Re: Change from Countx to DistinctCount in DAX Commands and Tips

Change from Countx to DistinctCount

doubleclick — Mon, 29 Nov 2021 12:29:03 GMT

Hi All,

I have a cool formula here but i'm having trouble converting it to a distinct count

I want to change the below into a distinctcount of a column called [Worker] - how can i change this?

Tenure by Day (Count over 365 days) = VAR SoM=min('Calendar'[Date]) VAR EoM=max('Calendar'[Date]) RETURN countx(CALCULATETABLE(MainTable,All('Calendar'),filter(MainTable,(DATEDIFF(MainTable[Start Date],EoM,DAY)>=365)),MainTable[Start Date]<=EoM,ABS( MainTable[End Date]>=SoM)),DATEDIFF(MainTableDE[Start Date],EoM,DAY))

Re: Change from Countx to DistinctCount

amitchandak — Mon, 29 Nov 2021 14:55:30 GMT

doubleclick , Try like

calculate( distinctcount(MainTableDE[Worker]) ,All('Calendar'),filter( MainTable,(DATEDIFF(MainTable[Start Date],EoM,DAY)>=365)),MainTable[Start Date]<=EoM,ABS(
MainTable[End Date]>=SoM))

Re: Change from Countx to DistinctCount

doubleclick — Mon, 29 Nov 2021 15:53:52 GMT

Thank you so much!!!! This fixed it - Issue Resolved!

Re: Change from Countx to DistinctCount

gotohbh — Tue, 25 Jul 2023 13:47:21 GMT

Hello,

i want transformed countx to distinctcount in this formule :

Count lots Months 2 = VAR __Table = GROUPBY('dw V_F_ObjetQuittanceSinistre','dw V_F_ObjetQuittanceSinistre'[DateReference_AnneeMois],"__Count",COUNTX (CURRENTGROUP(),[Lots]))

RETURN

MAXX(__Table,[__Count])