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 ...
  • smpa01's avatar
    smpa01
    4 years ago

    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
4 years ago

smpa01 

It worked perfectly well

Thank you so much!!!

Femi10's avatar
Femi10
Frequent Visitor
4 years ago

@smpa01 

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

Thanks

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    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.