Deactivate Date Relationship within Measure

Question

I am struggling a bit with a pretty simple measure at the moment. How can I deactivate a relationship between two tables? I have a fact table, that shows me all values for each item per date: Item - Value - Date - State - Type

This table is linked to the calendar table. In the end, I have pivot table that sums up the values per week. Now I need an additional column in this pivot table that shows me the total value for all items with a etate of "50", regardless of the date. I already have tried something like this:

=CALCULATE(SUM([Value]);ALL(Calendar);FILTER(tbl;tbl[State]=50))

This is not working.

How would you manage that?

I think the problem is the row context applied by the table visual. Read more about row context and filter context here: sqlbi.com/articles/row-context-and-filter-context-in-dax The ALLSELECTED function might help you here, but I would need to know more about your data model. — Christian Welsch

Ben Ben · Accepted Answer · 2020-04-12T06:41:58

The problem is the ALL(Calendar) part of the formula only impacts the SUM part, it doesn't change any of the other Filters in the CALCULATE, ie FILTER(tbl;tbl[State]=50) is still impacted by the particular Week Ending value for that row in your pivot table.

So to fix you can just shorten the FILTER(tbl;tbl[State]=50) to be tbl[State]=50

Deactivate Date Relationship within Measure

1 Answers