Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
Is there a way to use DAX filter/calculate function combination to return the value froma row from a certain using a running total/cumulative from a different column? I have a table dCaldenar like the following:
DateKey Workday Productivy
10/1/18 1 1.4
10/2/18 0 1.4
… … …
12/31/19 1 1.8
I would like to pass in a date measure to filter that table as follows:
Filter(dCalendar, dCalendar[DateKey]>= [DateMeasure] && dCalendar[Workday]=1)
Then I would like to somehow start summing the dCalender[Productivity] column from the filtered table, like a running total, and return the DateKey for the earlierst row where that running total is greater than or equal to some known value from another measure called [ProductivityNeeded].
Any other suggestions? Please help.
Thanks in advance.
Solved! Go to Solution.
Try adding an ALL() to the base table for the filter as below. Seems to work. Check out if it is so and then we can discuss what was at play.
Working Days Given := CALCULATE ( SUM ( dCalendar[Workday] ), FILTER ( ALL(dCalendar), dCalendar[Date] >= MIN ( fCommTime[ActualStart] ) && dCalendar[Date] <= MIN ( fCommTime[ActualEnd] ) && dCalendar[Workday] = 1 ) ) - SUM ( fCommTime[Days Lost] )
No worries. glad it helped.
Your code for the measure:
Working Days Given := CALCULATE ( SUM ( dCalendar[Workday] ), FILTER ( dCalendar, dCalendar[Date] >= MIN ( fCommTime[ActualStart] ) && dCalendar[Date] <= MIN ( fCommTime[ActualEnd] ) && dCalendar[Workday] = 1 ) ) - SUM ( fCommTime[Days Lost] )
When we invoke this measure within the other piece of code, we have a row context from the ADDCOLUMNS. As discussed earlier, I was afraid the context transition would play unwanted tricks. When I initially saw your code, though, it seemed fine because you are using the whole dCalendar table as base table for your filtering operation. That should be enough to override the effects of context transition. BUT, and here comes the interesting part, every time a measure is invoked, the engine wraps the measure in a CALCULATE. You probably are aware of that. So what we effectively have when we call your measure is:
CALCULATE ( CALCULATE ( SUM ( dCalendar[Workday] ), FILTER ( dCalendar, dCalendar[Date] >= MIN ( fCommTime[ActualStart] ) && dCalendar[Date] <= MIN ( fCommTime[ActualEnd] ) && dCalendar[Workday] = 1 ) ) - SUM ( fCommTime[Days Lost] ) )
The outermost CALCULATE does not have filter arguments and the filter resulting from context transition is applied fully. That filter is the current row of the table (the ADDCOLUMNS table), as you know. Then when the engine executes the inner CALCULATE we have that row as filter and that is applied directly to dCalendar in
FILTER(dCalendar;....)
The base table for the filter operation is just that one row instead of the full table that we would want. That is why you need the ALL( ).
Does that help?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |