Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Dynamic Measure depends on slicer value

Hi Power BI community,

I developed a measure column for calculating a cumulative data as per below code:

Cumulative Actual MH  =
 CALCULATE (sum ( 'PBI LabourCode_Actual'[Project Job numberMH] ),
 FILTER 
(ALL ( 'PBI LabourCode_Actual')
,'PBI LabourCode_Actual'[WorkDate] <= MAX ( 'PBI LabourCode_Actual'[WorkDate] )))

I want this measure to be depended to a slicer selection for Job Numbers (

PBI LabourCode_Actual'[Project Job numberMH])

, but so far I couldnt find anything to work . any suggestion? the job number values and measure column are  all in a same table. 

 

Really appreciate your help

Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago

    If you have a one to many relationship between Date and WorkDate (so the arrow is pointing from Date to WorkDate) you should be able to use the Date column to filter the 'PBI LabourCode_Actual' table. 

     

    In fact the typically pattern for building a cumulative "life to date" measure would be to use your date table in the filter.

     

    eg.

     

    Cumulative Actual MH =
    CALCULATE (sum ( 'PBI LabourCode_Actual'[Project Job numberMH] ),
    FILTER (ALL ( 'Date')
    ,'Date'[Date] <= MAX ( 'Date'[Date] )))

     

    Then slicers on any other tables should work normally.

5 Replies

  • d_gosbell's avatar
    d_gosbell
    Super User
    7 years ago

    The probem is that you are using ALL('PBI LabourCode_Actual') which will remove all filters including slicers. If you change your expression just to do ALL over the WorkDate column it should still calculate correctly, but also work with your Job Number slicer.

     

    Cumulative Actual MH  =
 CALCULATE (sum ( 'PBI LabourCode_Actual'[Project Job numberMH] ),
 FILTER 
(ALL ( 'PBI LabourCode_Actual'[WorkDate])
,'PBI LabourCode_Actual'[WorkDate] <= MAX ( 'PBI LabourCode_Actual'[WorkDate] )))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I applied your formula, but it didnt return the correct value. It returnes the Actual Manhour and not its cumulative  ! Look at the below table. I named your formula as Cumulative actual - New

       

      • d_gosbell's avatar
        d_gosbell
        Super User
        7 years ago

        What's that "Date" column on the rows? Is that WorkDate or some other date?

         

        You could try using ALLEXCEPT and excluding the column you are using for your slicer? (is it really [Project Job numberMH] as it seems strange to try to sum and filter by the one column?)

         

        Cumulative Actual MH  =
 CALCULATE (sum ( 'PBI LabourCode_Actual'[Project Job numberMH] ),
 FILTER 
(ALLEXCEPT ( 'PBI LabourCode_Actual', 'PBI LabourCode_Actual'[Project Job numberMH])
,'PBI LabourCode_Actual'[WorkDate] <= MAX ( 'PBI LabourCode_Actual'[WorkDate] )))

        This is the problem with having all your data in one big table. Having a separate 'Date' table makes things like cumulative sums a lot easier.