Solved: Re: Complicated DAX using SUMX

newgirl · ‎07-13-2020

Hi! I hope somebody can help me with my problem.

I have this file that I'm trying to figure out why the numbers are changing just by refreshing the data. Sadly, this file was just passed on from a Power BI developer that our company hired but his contract was not renewed. So they wanted our department to continue what the developer has built already but since the developer does not work with our company anymore, we can't ask him questions about the project. Also, I wasn't involved in the meetings when the turnover happened.

So this project reports KPIs of our product deliveries and I was appointed to specific KPIs only. One of the KPIs is to compute the number of hours that a truck has worked. We call it "Hours Worked Completed" and the developer placed in the file a matrix of the Hours Worked Completed per plate number.

Correct Data

The matrix is also filtered in which the Target Trips is greater than Zero. Below is the DAX code for the measure Target Trips.

Target Trips =
SUMX (
    VALUES ( CutOff[Date] ),
    SUMX (
        FILTER (
            fTCFleetNeg,
            CutOff[Date] >= fTCFleetNeg[From]
                && CutOff[Date] <= fTCFleetNeg[To]
        ),
        fTCFleetNeg[Trip_Incentives] / ( fTCFleetNeg[To] - fTCFleetNeg[From] + 1 )
    )
)

Illustration of the Data

Below is a sample data from one of the tables (fTMS) that lists down details of each trip. The KPI would compute how many hours the truck has worked.

DTSStage	time_inline	time_backinplant	date_inline	date_backinplant	RealPlate
COMPLETED	12:45:00 PM	1:30:00 AM	Tuesday, March 3, 2020	Thursday, March 5, 2020	ABC335
COMPLETED	2:17:00 PM	12:45:00 PM	Saturday, February 29, 2020	Tuesday, March 3, 2020	ABC335
COMPLETED	1:30:00 AM	2:00:00 AM	Thursday, March 5, 2020	Friday, March 6, 2020	ABC335
COMPLETED	6:51:00 AM	3:41:00 AM	Sunday, February 23, 2020	Friday, February 28, 2020	ABC335

Given this data, if I compute it manually, the truck worked for 248.55 hours. However, there is a slicer in the page that filters the date_backinplant from Feb 28 to March 29 only. Meaning within that period, the truck has worked for 135.40 hours only (248.55 less 17.15 and 96 because we did not anymore include the hours from Feb 23 to Feb 27).

DTSStage	time_inline	time_backinplant	date_inline	date_backinplant	RealPlate	Inline Hours	In Between	BackinPlant Hours
COMPLETED	12:45:00 PM	1:30:00 AM	Tuesday, March 3, 2020	Thursday, March 5, 2020	ABC335	11.25	24.00	1.50
COMPLETED	2:17:00 PM	12:45:00 PM	Saturday, February 29, 2020	Tuesday, March 3, 2020	ABC335	9.72	48.00	12.75
COMPLETED	1:30:00 AM	2:00:00 AM	Thursday, March 5, 2020	Friday, March 6, 2020	ABC335	22.50		2.00
COMPLETED	6:51:00 AM	3:41:00 AM	Sunday, February 23, 2020	Friday, February 28, 2020	ABC335	17.15	96.00	3.68

So anyway, the DAX formula that the developer created to compute for this is:

Hours Worked Completed =
CALCULATE (
    CALCULATE (
        SUMX (
            VALUES ( 'Calendar'[Date] ),
            SUMX (
                FILTER (
                    fTMS,
                    fTMS[DTSStage] = "COMPLETED"
                        && NOT ( ISBLANK ( fTMS[date_inline] ) )
                        && fTMS[date_inline] < 'Calendar'[Date]
                        && fTMS[date_backinplant] > 'Calendar'[Date]
                ),
                24
            )
        ),
        CROSSFILTER ( 'Calendar'[Date], fTMS[date_backinplant], NONE )
    )
        + CALCULATE (
            SUMX (
                VALUES ( 'Calendar'[Date] ),
                SUMX (
                    FILTER (
                        fTMS,
                        fTMS[DTSStage] = "COMPLETED"
                            && NOT ( ISBLANK ( fTMS[date_inline] ) )
                            && fTMS[date_inline] = 'Calendar'[Date]
                            && fTMS[date_backinplant] <> 'Calendar'[Date]
                    ),
                    24 * ( 1 - fTMS[time_inline] )
                )
            ),
            CROSSFILTER ( 'Calendar'[Date], fTMS[date_backinplant], NONE )
        )
        + CALCULATE (
            SUMX (
                VALUES ( 'Calendar'[Date] ),
                SUMX (
                    FILTER (
                        fTMS,
                        fTMS[DTSStage] = "COMPLETED"
                            && NOT ( ISBLANK ( fTMS[date_inline] ) )
                            && fTMS[date_inline] <> 'Calendar'[Date]
                            && fTMS[date_backinplant] = 'Calendar'[Date]
                    ),
                    24 * ( fTMS[time_backinplant] )
                )
            ),
            CROSSFILTER ( 'Calendar'[Date], fTMS[date_backinplant], NONE )
        )
        + CALCULATE (
            SUMX (
                VALUES ( 'Calendar'[Date] ),
                SUMX (
                    FILTER (
                        fTMS,
                        fTMS[DTSStage] = "COMPLETED"
                            && NOT ( ISBLANK ( fTMS[date_inline] ) )
                            && fTMS[date_inline] = 'Calendar'[Date]
                            && fTMS[date_backinplant] = 'Calendar'[Date]
                    ),
                    24 * ( fTMS[time_backinplant] - fTMS[time_inline] )
                )
            ),
            CROSSFILTER ( 'Calendar'[Date], fTMS[date_backinplant], NONE )
        ),
    CROSSFILTER ( CutOff[Date], 'Calendar'[Date], BOTH )
)

Here is actually the model view in which I highlighted in green the involved tables and the fields with relationship to each other. I just blocked the other tables that are not used by the measures involved.

Model View

Sample data from other involved tables:

Table ftcFleetNeg. - is data filled in monthly but the dates are only "From" and "To"

From	To	P_NO
7/1/2019	7/31/2019	Plate A
6/1/2019	6/30/2019	Plate A
3/1/2019	3/31/2019	Plate A
5/1/2019	5/31/2019	Plate A
4/1/2019	4/30/2019	Plate A
8/1/2019	8/31/2019	ABC335
7/1/2019	7/31/2019	ABC335
6/1/2019	6/30/2019	ABC335
9/29/2019	10/29/2019	ABC335
9/1/2019	9/28/2019	ABC335
10/30/2019	11/28/2019	ABC335
11/29/2019	12/27/2019	ABC335
1/30/2020	2/27/2020	ABC335
12/28/2019	1/27/2020	ABC335
2/28/2020	3/29/2020	ABC335

Table RealPlateList - is just a created table in which "RealPlate_List = DISTINCT(fTMS[RealPlate])"

Table Calendar - basic calendar table

Table Cutoff - is like a Calendar table but has a column that classifies which dates would fall into their "cut off period". For example, they labeled "March" dates from Feb 28 to March 29.

So what's weird is that using the original file that the developer passed on to us which I did not yet make changes, after I refresh it, the numbers shown in the matrix would change, like the one below. Below are mixed samples with correct data still (Plate A) and three other plate numbers with changed numbers (Plate B, C, and ABC335). Overall, most of the plate numbers' hours did not actually change except for Plate B, Plate C, and ABC335.

wrong matrix except for Plate A data

If I compare it with the correct matrix visual, it seems like there are 24 hours being added on other days even though the truck did not travel on that day. For example for ABC335 within March, it only has March 3, March 5, and March 6 in field date_backinplant. So I don't know why it's "filling out" 24 hours outside of those dates.

It seems like this is the reason on why the hours increased after refreshing but I couldn't find the factor on what's causing this "filling out" of 24 hours given that I've already compared the tables from before refreshing and after refreshing, and the only changes are the new data after March.

Would you have an idea on what factor that's causing this? What else can I check? I hope somebody will be able to understand. I'm already at wit's end.

Anonymous · ‎07-17-2020

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.

View solution in original post

Anonymous · ‎07-14-2020

Instead of the measure in your post, can you try the following simplified measure?

HoursWorkedCompleted =
VAR DurationDT =
    SUMX (
        fTMS,
        ( fTMS[date_backinplant] + fTMS[time_backinplant] ) - ( fTMS[date_inline] + fTMS[time_inline] )
    )
RETURN
    DurationDT * 24

newgirl · ‎07-14-2020

I tried your suggested formula with some filters that I added:

HoursWorkedCompleted = 
VAR DurationDT =
    SUMX (
        FILTER(fTMS, fTMS[DTSStage]= "COMPLETED" && NOT (ISBLANK(fTMS[date_inline]))),
        ( fTMS[date_backinplant] + fTMS[time_backinplant] ) - ( fTMS[date_inline] + fTMS[time_inline] )
    )
RETURN
    DurationDT * 24

It does correctly compute per row. However, the page has a slicer of period wherein it would let the user see how many hours were completed based on the given period. So if I try your measure and show the hours per day, it would show this:

By looking at plate ABC335 under Feb 28, technically it's correct because the total hours worked for that trip is 116.83 hours but the trip started from Feb 23 and ended on Feb 28. Thus, the expected number under Feb 28 should be 3.68 hours.

I'm guessing this is why developer had to create a long DAX formula. The problem is that I'm not used to sumx, especially if it's a sumx within a sumx so I can't quite trace on what's causing the additional 24 hrs in the matrix after I refresh.

time_inline	time_backinplant	date_inline	date_backinplant	RealPlate	.	.	.	Total
12:45:00 PM	1:30:00 AM	Tuesday, March 3, 2020	Thursday, March 5, 2020	PUX335	11.25	24.00	1.50	36.75
2:17:00 PM	12:45:00 PM	Saturday, February 29, 2020	Tuesday, March 3, 2020	PUX335	9.72	48.00	12.75	70.47
1:30:00 AM	2:00:00 AM	Thursday, March 5, 2020	Friday, March 6, 2020	PUX335	22.50	2.00		24.50
6:51:00 AM	3:41:00 AM	Sunday, February 23, 2020	Friday, February 28, 2020	PUX335	17.15	96.00	3.68	116.83

Anonymous · ‎07-14-2020

.

Anonymous · ‎07-14-2020

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

newgirl · ‎07-15-2020

Hi, @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 * 24

I 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. 😞

Anonymous · ‎07-17-2020

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 · ‎07-19-2020

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!

Anonymous · ‎07-14-2020

I would like to recreate your scenario on my PBI Desktop. Will you be able to send some sample data. I have tried using the sample data you have posted. It seems it is inadequate.

I have the following sample data tables from your post.

1) Calendar

2) ftcFleetNeg

3) fTMS

4) RealPlateList

If you think these data is enough to recreate the scenario, please send some relevant entries for the following table also

"Table Cutoff - is like a Calendar table but has a column that classifies which dates would fall into their "cut off period". For example, they labeled "March" dates from Feb 28 to March 29."

The CutOff table is also used in the DAX code. So I will need some relevant data for this table also.

Further, You said there is a date filter on the report. Is it filtered based on "Calendar[Date]" or fTMS[date_backinplant] ?

It will be helpfult if you can create a pbix file with all sample data and DAX codes so that we can easily fix it.

newgirl · ‎07-14-2020

Hi, @Anonymous ! I copied only the relevant columns from the database and uploaded in pbix file. I also already put in the measures.

Another weird thing I find is that even if I try to replicate this part of the report like this one, I still don't get the correct numbers even though I just copy the measures and the data. But anyway, the output in the sample pbix has the same "sickness" wherein there's 24 hours being shown on days that the truck had no trips.

https://drive.google.com/file/d/1xpJyjpu54H0Penra0PJ124MsguGtdXMl/view?usp=sharing