Forum Discussion
Post PatronComplicated DAX using SUMX
Actually, you should ignore all my posts. The formula written by the developer is good. There is no error in it.
Earlier, I overlooked one point. It is in the following line of code.
CROSSFILTER ( 'Calendar'[Date], fTMS[date_backinplant], NONE )The explanation is, there is a relationship between "Calendar[Date]" field to "ftms[date_backinplant]" field. So when you plot the measure to a matrix with "Calendar[Date]" on columns and "fmts[RealPlate]" on the rows, these two fields filter the fTMS table and as a result, only those records with "date_backinplant" and "realplate" has records on any given date is passed on to the calculation. But you need the "24 Hours" for all the dates betwen "date_inline" and "date_backinplant". To do this, the filter from "Calendar[Date]" field on "ftms[date_backinplant]" had to be removed. It was done using CROSSFILTER() function with "NONE" as the 3rd paramenter which removes the filter from "Calendar[Date]" on fTMS table.
All is perfect till now. But the problem arised when you had the following record in your data.
DTSStage time_inline time_backinplant date_inline date_backinplant RealPlate COMPLETED 02:00:00 21:24:00 06-03-2020 08-06-2020 ABC335 In this record, the date_backinplant has a value of 6th Jun, but date_inline has a value of 6th Mar, and because of the CROSSFILTER with "NONE" as the parameter, this record was not filtered out although date_backinplant's 6th Jun date falls outside your date selection. Therefore, from 6th Mar 2020 onwards, for every day, the formula inserted 24 Hours till the last day in your matrix which is 29th March 2020.
If you want to omit the records with "date_backinplant" falling outside the range of your selected date range, you will have to exclusively add a filter to your fTMS table in the code somewhere near the filter on "COMPLETED" stage.
Try this Measure..
HoursWorkedCompleted =
VAR CurrentPeriod =
CALCULATETABLE ( VALUES ( 'Calendar'[Date] ), ALLSELECTED () )
VAR DurationDT =
SUMX (
FILTER (
fTMS,
fTMS[DTSStage] = "COMPLETED"
&& NOT ( ISBLANK ( fTMS[date_inline] ) )
),
IF (
fTMS[date_inline] IN CurrentPeriod,
( fTMS[date_backinplant] + fTMS[time_backinplant] ) - ( fTMS[date_inline] + fTMS[time_inline] ),
MOD ( fTMS[time_backinplant], 1 )
)
)
RETURN
DurationDT * 24
Post PatronHi, Anonymous !
Thank you so much for taking the time to explain the formula! I will definitely keep coming back to your explanation as reference when I get lost in "visualizing" the formula structure. I'm also not sure if you noticed it but I included in one of my replies the link to the pbix sample?
Anyway, as for the 2nd measure you suggested,
HoursWorkedCompleted =
VAR CurrentPeriod =
CALCULATETABLE ( VALUES ( 'Calendar'[Date] ), ALLSELECTED () )
VAR DurationDT =
SUMX (
FILTER (
fTMS,
fTMS[DTSStage] = "COMPLETED"
&& NOT ( ISBLANK ( fTMS[date_inline] ) )
),
IF (
fTMS[date_inline] IN CurrentPeriod,
( fTMS[date_backinplant] + fTMS[time_backinplant] ) - ( fTMS[date_inline] + fTMS[time_inline] ),
MOD ( fTMS[time_backinplant], 1 )
)
)
RETURN
DurationDT * 24I added this measure in the original file (without refreshing) and here's a comparison between the 2 tables (your measure is on the 2nd table). The Grand total per plate is correct but the hours per day is incorrect.
Taking ABC335 as an example and looking at the data with manual computation, it looks like your formula is adding per trip and showing them on the day of the date under Back In Plant. Like how 70.47 hours is being recorded on March 3 in the 2nd table when in fact, for March 3, the truck worked for 24 hours (11.25 + 12.75), as shown in 1st table.
Just to reiterate my problem, when I download the original file he left us and without editing anything, there doesn't seem to be any problem with the numbers. But after I refresh but still without moving any filter or editing anything, the numbers of only 3 plate numbers changed, wherein it looks like additional 24 hours were being "added" on the rest of the days even though they didn't have any trips on that day. Aside from these 3 plate numbers, the other hundreds of plate numbers are showing the correct no. of hours worked per day.
To try tracing the factor of causing the changes, I tried downloading the tables connected to the measures from the PBIX before refreshing and after refreshing to compare them to each other but there doesn't seem to have any difference.
I wanted to have another perspective on how to approach in tracing the problem but I've already tried and tested several things but I still can't find it. 😞
Actually, you should ignore all my posts. The formula written by the developer is good. There is no error in it.
Earlier, I overlooked one point. It is in the following line of code.
CROSSFILTER ( 'Calendar'[Date], fTMS[date_backinplant], NONE )The explanation is, there is a relationship between "Calendar[Date]" field to "ftms[date_backinplant]" field. So when you plot the measure to a matrix with "Calendar[Date]" on columns and "fmts[RealPlate]" on the rows, these two fields filter the fTMS table and as a result, only those records with "date_backinplant" and "realplate" has records on any given date is passed on to the calculation. But you need the "24 Hours" for all the dates betwen "date_inline" and "date_backinplant". To do this, the filter from "Calendar[Date]" field on "ftms[date_backinplant]" had to be removed. It was done using CROSSFILTER() function with "NONE" as the 3rd paramenter which removes the filter from "Calendar[Date]" on fTMS table.
All is perfect till now. But the problem arised when you had the following record in your data.
DTSStage time_inline time_backinplant date_inline date_backinplant RealPlate COMPLETED 02:00:00 21:24:00 06-03-2020 08-06-2020 ABC335 In this record, the date_backinplant has a value of 6th Jun, but date_inline has a value of 6th Mar, and because of the CROSSFILTER with "NONE" as the parameter, this record was not filtered out although date_backinplant's 6th Jun date falls outside your date selection. Therefore, from 6th Mar 2020 onwards, for every day, the formula inserted 24 Hours till the last day in your matrix which is 29th March 2020.
If you want to omit the records with "date_backinplant" falling outside the range of your selected date range, you will have to exclusively add a filter to your fTMS table in the code somewhere near the filter on "COMPLETED" stage.
- newgirl
Hi Anonymous !!! Thank you so much for your reply! You were right, it's that type of transaction that was affecting the "additional" 24 hours when the data was updated. I wasn't able to locate this source of discrepancy since I was limiting my checking of data within the March backinplant dates. Again, thank you so much! I'm so grateful. You were very helpful and your replies were very informative!