topic Dax Calculation in DAX Commands and Tips

Dax Calculation

Femi10 — Wed, 03 Nov 2021 18:10:46 GMT

To find out how many times a Job ID appears in my Data set, I used the formula

COUNTROWS(FILTER('Engrs Timesheet', EARLIER('Engrs Timesheet'[Job ID],1) = ('Engrs Timesheet'[Job ID]))) in the column VISITCOUNT2

I further want to streamline my analysis to count only job numbers that appear multiple times on different days and (not on the same date)

So here is a sample of my Data

I want to write a function that counts the number of times a Job number appears into the VisitCount2 column, but I only want it to count it as two if the job numbers are not on the same On-site date.

That is D0000178 should be counted as 3 rather than 4 because two of its elements have the same On-site date (11/10/2021) same as M00002062 which should be counted as 2 instead of 3 because two of its elements have same On-site date (26/10/2021)

Thank you all

Re: Dax Calculation

smpa01 — Wed, 03 Nov 2021 20:37:35 GMT

Femi10 you can try this

Column = CALCULATE ( COUNTX ( SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[On Site] ), 'Table'[On Site] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )

pbix is attached

Re: Dax Calculation

Femi10 — Wed, 03 Nov 2021 23:28:41 GMT

smpa01

It worked perfectly well

Thank you so much!!!

Re: Dax Calculation

Femi10 — Thu, 04 Nov 2021 10:47:32 GMT

@smpa01

Could you please share a brief explanation on how you came about this solution.

Thanks

Re: Dax Calculation

smpa01 — Thu, 04 Nov 2021 14:13:18 GMT

Femi10 sure.

If you dissect the code, it has three parts

Column = CALCULATE ( COUNTX ( SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[On Site] ), 'Table'[On Site] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )

the inner most part is SUMMARIZE, the immediate outer part is COUNTX and outer most part is Calculate with partition(ALLEXCEPT).

SUMMARIZE generates a distinct table with all unique combinations of ID and Site which looks like this

COUNTX has an iterator and if I pass on the following it generates this which is the total count of all the rows from that summarized table

CALCULATE being the modifier, the partition by which the count needs to be performed can be mentioned and it gives the desired result.