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.

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.