Forum Discussion

palvarez83's avatar
palvarez83
Helper 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?               

AlB's avatar
AlB
Community Champion
7 years ago

Hi palvarez83

 

How about the following. I have not tested as I don't have your model but it gives you the general idea.

The innermost FILTER is what you provide. Then we add a column calculating the running total and lastly filter [ProductivitNeeded]. For the other version (bottom to top)  you just change the <= operator in
dCalendar[DateKey] <= EARLIER ( dCalendar[DateKey] )  
   for >=, i.e.
dCalendar[DateKey] >= EARLIER ( dCalendar[DateKey] )

 

Be careful with the measures [DateMeasure] and [ProductivityNeeded] as they are being invoked in row context and will trigger context transition. You might have to expand the code to avoid the implicit CALCULATE.

 

FILTER (
    ADDCOLUMNS (
        FILTER (
            dCalendar;
            dCalendar[DateKey] >= [DateMeasure]
                && dCalendar[Workday] = 1
        );
        "RunningTotal"; CALCULATE (
            SUM ( dCalendar[Productivity] );
            ALL ( dCalendar );
            dCalendar[DateKey] <= EARLIER ( dCalendar[DateKey] )
        )
    );
    [RunningTotal] >= [ProductivityNeeded]
)

 

  • palvarez83's avatar
    palvarez83
    Helper I
    7 years ago

    AlB,

     

    Thank you.  This looks promising.  I'm working on it.  A couple of questions.

     

    1.  It seems like the formula you suggest would return a table.  Is that correct?  Would I then need to enclosure the min() function to return the earliest dCaldendar[DateKey] from that column of the filtered table with added column?

     

    2.  Dumb question:  is there a difference when writting DAX with semicolons like in the code you suggested vs. commas?  I had not seen the semicolons before.

     

     

    • palvarez83's avatar
      palvarez83
      Helper I
      7 years ago

      AlB,

      Yes, it appears it returns a table....   I test it and the running totals seem to work fine.

       

      I am stuck on how to return the [DateKey]  column from the table created by the code you suggested, so I can then apply a Min() or Earliest() function to return the first/ earliest/minimum datekeyvalue.

       

      Any suggestions?

       

      Thank you,

      • AlB's avatar
        AlB
        Community Champion
        7 years ago

        Hi palvarez83

        Yeah it's a table.

        I'm just realizing that I misread your question. Well, I actually think I read it correctly but then somehow I forgot a part of it when I was putting the code together and left it incomplete. Sorry about that.

        Try the following for your first scenario. I've only added the SELECTCOLUMNS and FIRSTNONBLANK. I am going through an existential crisis :smileyvery-happy: :smileyvery-happy: with FIRSTNONBLANK, I yesterday discovered that it works in a different way that I thought, but I believe it will work in this case.

        The other scenarios should be a combination of FIRST-LASTNONBLANK and the <= , >=  in the condition as explained in the initial post.

        Let me know if it works

         

        DateKeyTopBottom =
FIRSTNONBLANK (
    SELECTCOLUMNS (
        FILTER (
            ADDCOLUMNS (
                FILTER (
                    dCalendar;
                    dCalendar[DateKey] >= [DateMeasure]
                        && dCalendar[Workday] = 1
                );
                "RunningTotal"; CALCULATE (
                    SUM ( dCalendar[Productivity] );
                    ALL ( dCalendar );
                    dCalendar[DateKey] <= EARLIER ( dCalendar[DateKey] )
                )
            );
            [RunningTotal] >= [ProductivityNeeded]
        );
        "DateKey2"; [DateKey]
    );
    1
)