CODEWITHSUNDEEP
daxpowerpivot
Mark
Mark

Reputation: 11

Finding Min Value Filtering Two Columns

In PowerPivot I'm trying find the Min value in a table based on two column values. My data contains multiple people, on multiple dates with multiple segments of time. For each person on each date I need to determine the earliest segment of time in the data.

I was able to use this in data on a daily basis and it works fine but when I add multiple days to the data it only locates the minimum segment of time for each person, not each person for each date listed.

=
IF (
    CALCULATE (
        MIN ( [start] ),
        FILTER ( actual, [Agent_User_Name] = EARLIER ( [Agent_User_Name] ) )
    )
        = [start],
    TRUE (),
    FALSE ()
)

    Agent_User_Name     date      start
    Susan               1/1/19    08:00
    Susan               1/1/19    08:45
    Susan               1/1/19    10:00
    Susan               1/2/19    08:30
    Susan               1/2/19    09:00
    Timothy ....

I am trying to determine that Susan's earliest time on 1/1/19 was 08:00, on 1/2/19 was 08:30 and mark that record as true or false for earliest time.

Upvotes: 1

Views: 1578

Answers (1)

RADO
RADO

Reputation: 8148

If you want to make a calculated column:

=
VAR Min_Time =
    CALCULATE (
        MIN ( Actual[Start] ),
        ALLEXCEPT ( Actual, Actual[Agent_User_Name], Actual[date] )
    )
RETURN
    IF ( Actual[Start] = Min_Time, TRUE (), FALSE () )

Here, for each row we find min start in the entire start column (that's why we use ALL), but keeping current row's Agent_User_Name and Date filters (that's why EXCEPT). In effect, it groups the start column by current user and date.

Result:

enter image description here

You can also make it a dynamic measure:

=
MINX (
    SUMMARIZE (
        Actual,
        Actual[Agent_User_Name],
        Actual[date],
        "Min_Time", MIN ( Actual[start] )
    ),
    [Min_Time]
)

Result:

enter image description here

Edit:

VAR works for Excel 2016 or later. If you have older versions of Excel, re-write the formula without variables:

= IF (
        Actual[Start]
            = CALCULATE (
                MIN ( Actual[Start] ),
                ALLEXCEPT ( Actual, Actual[Agent_User_Name], Actual[date] )
            ),
        TRUE (),
        FALSE ()
    )

Not as clean and readable as with VAR, but should work. If you can, switch to Excel 2016 as soon as possible - Power Pivot engine is much better.

Upvotes: 1

Related Questions