Forum Discussion

newgirl's avatar
newgirl
Icon for Post Patron rankPost Patron
6 years ago
Solved

Complicated DAX using SUMX

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...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

     

    DTSStagetime_inlinetime_backinplantdate_inlinedate_backinplantRealPlate
    COMPLETED02:00:0021:24:0006-03-202008-06-2020ABC335

     

    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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    newgirl
    Icon for Post Patron rankPost Patron
    6 years ago


    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_inlinetime_backinplantdate_inlinedate_backinplantRealPlate .  .  .  Total 
    12:45:00 PM1:30:00 AMTuesday, March 3, 2020Thursday, March 5, 2020PUX335            11.25      24.00         1.50      36.75
    2:17:00 PM12:45:00 PMSaturday, February 29, 2020Tuesday, March 3, 2020PUX335              9.72      48.00      12.75      70.47
    1:30:00 AM2:00:00 AMThursday, March 5, 2020Friday, March 6, 2020PUX335            22.50         2.00       24.50
    6:51:00 AM3:41:00 AMSunday, February 23, 2020Friday, February 28, 2020PUX335            17.15      96.00         3.68    116.83
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      .

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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