Forum Discussion
Lookup DateKey from another column's running total?
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?
I played around by moving the location of the filters and I get the correct intermediate table as follows:
TroubleshootProductivityRunningTotal =
FILTER (
ADDCOLUMNS (
dCalendar,
"RunningTotal", CALCULATE (
SUM ( dCalendar[Productivity] ),
FILTER (
ALL ( dCalendar ),
dCalendar[Date] > DATE ( 2019, 1, 11 )
&& dCalendar[Workday] = 1
&& dCalendar[Date] <= EARLIER ( dCalendar[Date] )
)
)
),
[RunningTotal] >= 30.6
)Even when I enclose that with the SELECTCOLUMNS function and then the FIRSTNONBLANK() function, I get the correct answer...
However, I run into trouble when I need to change the [RunningTotal] >= 30.6 to a use a measure [RunningTotal]>= [RunningTotalNeeded]. It appears to be evaluating the [RunningTotalNeeded] in the wrong context and I am not sure how to correct it. I suspect it has to do something with context transition as you mentioned before. Thank you for your ongoing support.
Hi palvarez83
Setting all filtering conditions together as you've done is a smart a quick solution. The problem with doing the filtering (in red) in the first parameter of the ADDCOLUMNS, as we had done initially, is that come the CALCULATE we have no direct way to refer to that filtered version, since dCalendar is interpreted by the engine as the full table.
ADDCOLUMNS (
FILTER (
dCalendar;
dCalendar[DateKey] >= [DateMeasure]
&& dCalendar[Workday] = 1
);
"RunningTotal"; CALCULATE (
SUM ( dCalendar[Productivity] );
ALL ( dCalendar );
dCalendar[DateKey] <= EARLIER ( dCalendar[DateKey] )
)Yeah, most likely it is something related to context transition with that measure. Can you show the code for [RunningTotalNeeded]? Then I'd be able to do more.
Maybe just expanding the code for the measure would suffice.
I was wondering, are the filtering conditions
dCalendar[Date] > DATE ( 2019, 1, 11 ) && dCalendar[Workday] = 1fixed? If they are not and you're interested in seeing the results for other scenarios I think it would be better to do the filtering through slicers. Then you can change the conditions dynamically.
Relationships between fCommTime and dCalendar? Are those two the only tables in your model?
Would it be possible to share the pbix?
you can just share here the URL to the file from platforms like Dropbox, OneDrive etc. or just upload it to a site like this (no sign in required) and post the URL here
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?
Another option, albeit probably less aesthetically appealing, would be to expand the code for the measure and use it directly instead of invoking the measure. This would eliminate the implicit CALCULATE, thus rendering the ALL() unnecessary:
DateNeed := FIRSTNONBLANK ( SELECTCOLUMNS ( FILTER ( ADDCOLUMNS ( dCalendar, "RunningTotal", CALCULATE ( SUM ( dCalendar[Workday] ), FILTER ( ALL ( dCalendar ), dCalendar[Date] > [PD Date] && dCalendar[Workday] = 1 && dCalendar[Date] <= EARLIER ( dCalendar[Date] ) ) ) ), [RunningTotal] >= CALCULATE ( SUM ( dCalendar[Workday] ), FILTER ( dCalendar, dCalendar[Date] >= MIN ( fCommTime[ActualStart] ) && dCalendar[Date] <= MIN ( fCommTime[ActualEnd] ) && dCalendar[Workday] = 1 ) ) - SUM ( fCommTime[Days Lost] ) ), "DateKey", dCalendar[Date] ), 1 )and one thing that we haven't commented on but caught my attention from the beginning: why do you have the 'Productivity' column in your date table, dCalendar?
AlB,
Good question. I am working on a large project in Asia that is made up of smaller projects (modules) where we have to do some commissisoning one each module after it is built. We get a forecasted or actual start and end date to do commissioning on each module as a given. Each module has a planned commissioning duration which right now usual exceeds the given window that we are given. So I need to answer a few questions.
1. In that given window how much uncomplete work would I have left over for each module? I compute this as the
( [NeededWorkWorkdays]- [WorkingDaysGiven] ) / [NeededWorkWorkDays] * [Man-hours to be worked]...
2. If there is going to be work left over, by what date in the future could we complewte it? That/s where you helped me sum working days using the ADDCOLUMNS function. I pull the calendar table fillter based on dates after the end date I'm given and using a running total I figure out how many working days I need to accumulate and return that date.
That was the summing of workdays part. The zeros represent sundays and public holidays. Next came the productivity factors summation.
Since we are trying to complete this left over work as quickly as possible, our team has been working overtime 1.4 producitfy factor instead of just 1.0 which represent the assumed working hours baseline. Then starting certain dates, we are forecasting a partial night shfit to start and give the dayshift guys a break from overtime work ( 1.6 productivyt factor) and then a full night**bleep** at a later date which we assume will only be 80% as productiy as the day shift so you would have 1.8 as that day's productivy. So the dates which these changes in producity happen apply across all modules. So I imported them from an excel table, merged these factors onto the calendar table using power query by matching dates and filled downwards. Then any remaining null values got replaced with 1.0 (default)
By summing the daily producity factors from the calendar table filtered for working days only, I can answer the same questions as above, for the nightshift scenario: How much work will be leftover and if given an extension, up until which date I would need it. This would help us forecast when we actually need nightshift to complete the work.
Since the productiy factor is an assumption that depends on date only and is to be referenced in 4 different calculations for each module, it made sense to have it in the Calendar table. One possible improvement I might do next is adjust producity factors based on how many simulaneous modules are available to work on on a given date, but this is good enought for now. Perhaps there would have been a more efficent way of doing this, but I'm reactiving to what I have and it has been a fun experience for me to start learning DAX, powerquery, and Power Bi.
Thank you for all your help!
If you don't know the Italian gurus yet, they have a wealth of very interesting stuff here.
Their articles/blogs and books on DAX are really good.
dCalendar[Workday] = 1 is a fixed condition.
dCalendar[Date] > DATE ( 2019, 1, 11 ) wiill be replaced by a measure dCalendar[Date] > [DateNeeded]. That measure does not seem to be affected by context transition in what I am trying to do now.
There are 2 versions of this that I am trying to run... the one I am referencing above is summing dCalendar[Productivity]. The simpler version is very similar, but it is summing dCalendar[Workday]. Here is the code for that one:
DateNeed := FIRSTNONBLANK ( SELECTCOLUMNS ( FILTER ( ADDCOLUMNS ( dCalendar, "RunningTotal", CALCULATE ( SUM ( dCalendar[Workday] ), FILTER ( ALL ( dCalendar ), dCalendar[Date] > [PD Date] && dCalendar[Workday] = 1 && dCalendar[Date] <= EARLIER ( dCalendar[Date] ) ) ) ), [RunningTotal] >= [RunningTotalNeeded] ), "DateKey", dCalendar[Date] ), 1 )The [PD Date] measure is not problematic right now, but the [ RunningTotalNeeded] measure is.
The code for the problematic meaure is:
RunningTotalNeed :=
MAX ( [Baseline HVAC Work Days] - [Working Days Given], 0 )Where those two mearures are calculated as follows:
Baseline HVAC Work Days:=sum ( fCommTime[Planned Work Days] )
and
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] ),
No relationship between fCommTime and dCalendar. There is one more table, dModule that is used for slicers and it is related to fCommTime.
Orignially, I was creating this in PowerPivot, but I imported it to PowerBi and recreated the table that I am having issues with. Link is below. Sorry for he lack of a proper measure table (don't know how to do that in PowerPivot).
The visualization has a table I used to replecate the pivot table that was on power pivot. The last column [Ext. calculation] is the one we are trouble shooting. This one is missing an if statment to return an alternative answer when the [VAR wo N/S] measure is equal to zero.
The second to last column is the problematic measure that is having trouble filtering [VAR wo N/S] . It should be evaluating like it is on the table column, but in the [Ext. Calulation] .
The 3rd to last column is the correct answer (when the problematic measure is not equal zero). So this one should match the last column if the 2nd to last column does not equal zero. I will fix it with an if stament later.
Thank you for your help.
Can't access the link you just posted. Maybe you can share the pbix itself? Possibly with dummy data if there are confidentiality issues
No confidentiality issues. Since the link didn't work, is there a way to send an attachment? Or how else can I send the PBIX file?
Is there an email I can send it to or how are these normally shared?
Thank you.
Wow! That work. I also went back through the other version and added the ALL() and it too worked. Can you explain what is at play there?
Thank you so much!!
AlB , Yes. This helps a lot. I had heard of the implicite calculate, but could never figure out what that meant, even with a google search. Thank you for taking the time to explain this to me.
AlB, Thanks again. I wasn't aware that expanding the measure code had a different behavior than invoking the measure. So that is a good to know thing as well.
AlB, Done. Kudos given. :)
:smileyvery-happy::smileyvery-happy: :smileyvery-happy:Wow, that was a bit over the top. Thanks