Forum Discussion

palvarez83's avatar
palvarez83
Icon for Helper I rankHelper I
7 years ago
Solved

Lookup DateKey from another column's running total?

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

    palvarez83

     

    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] )

     

  • AlB's avatar
    AlB
    7 years ago

    palvarez83

    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?               

palvarez83's avatar
palvarez83
Icon for Helper I rankHelper I
7 years ago

AlB,

Thank you so much for the help so far.  Looks like we're making some headway.  I am using a table created from the DAX formula help troubleshoot each step by seeing the intermediate tables generated. 

 

TroubleshootProductivityRunningTotal =
ADDCOLUMNS (
    FILTER (
        dCalendar,
        dCalendar[Date] >= DATE ( 2019, 1, 11 )
            && dCalendar[Workday] = 1
    ),
    "RunningTotal", CALCULATE (
        SUM ( dCalendar[Productivity] ),
        ALL ( dCalendar ),
        dCalendar[Date] <= EARLIER ( dCalendar[Date] )
    )
)

 I think I found what my issue is.  When I use the ADDCOLUMNS function, the CALCULATE expression is computing the [RunningTotal] for each row, based on the original, unfiltered dCalendar table, instead of computing it based on the filtered table created by the FILTER function. 

 

 

Is there a easy way to correct this?   If I remove ALL(dCalendar), it returns the same (incorrect) running total for each row.

 

Thank you.

palvarez83's avatar
palvarez83
Icon for Helper I rankHelper I
7 years ago

AlB

 

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.

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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.     

     

    • AlB's avatar
      AlB
      Icon for Community Champion rankCommunity Champion
      7 years ago

      palvarez83

      I was wondering, are the filtering conditions

       

      dCalendar[Date] > DATE ( 2019, 1, 11 )
                    && dCalendar[Workday] = 1

       fixed? 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.    

      • palvarez83's avatar
        palvarez83
        Icon for Helper I rankHelper I
        7 years ago

        AlB

         

        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.

         

         

    • palvarez83's avatar
      palvarez83
      Icon for Helper I rankHelper I
      7 years ago

      AlB

       

      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] )
      • AlB's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        7 years ago

        palvarez83

        Relationships between fCommTime and dCalendar? Are those two the only tables in your model?

        Would it be possible to share the pbix?