Forum Discussion

Femi10's avatar
Femi10
Frequent Visitor
4 years ago
Solved

Dax Calculation

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

  • 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

     

     

4 Replies

  • smpa01's avatar
    smpa01
    Community Champion

    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's avatar
        Femi10
        Frequent Visitor

        @smpa01 

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

        Thanks