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

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.