Dax Calculation

[Femi10]

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

[smpa01 · Accepted solution]

@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

[Femi10]

@smpa01 

It worked perfectly well

Thank you so much!!!

[Femi10]

@smpa01 

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

Thanks

[smpa01]

@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.