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] ), ...
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.

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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Ok, I think I know what the issue is . The Date" column on the rows is not WorkDate. Actually it is date from a calendar table that I created and made a relationship between it and WorkDate. I changed the rows to WorkDate and the formula is working now.

       

      However, for some reason I want to use calendar date for columns not WorkDate. any suggestion?

      • d_gosbell's avatar
        d_gosbell
        Super User
        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.