cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
waeltken
Frequent Visitor

FILTER statement to return most recent record for each User ID - as-of date

To the PBI Community,

 

I'm stuck with what seems like it should be a simple common-place operation in DAX expressions. I have a table of effective dated employee records which chronicle each employee's history in our HR system. I'm trying to build a FILTER expression to wrap in a number of CALCULATE-based measures to calculate people metrics (e. g. active headcount) for different as-of dates. Basically the working measure will allow me to calculate all active employees or subsets thereof for any requested current or past effective dates: as-of end of Q1, as of end of fiscal year etc. because the table includes all historical records for all employees.

 

While I have been able to write a statement excluding future dated records vis-a-vis the effective date (as-of date), I'm struggling to write another expression which would filter the records table down to the latest record on file per USER ID. My current expression passes all records which are on or prior to the as-of-date to my CALCULATE function and when I filter out "Terminated" records my active headcount measure still shows some folks as active erroneously owing to that factor. 

 

Please see my measure and data table below. Please note that in the above mentioned example, as-of 4/10/2021 my measure should calculate 2 active headcount (as the employee Junique would have gone to status terminated prior to that date). I've shaded in RED, which records should be passed to CALCULATE from FILTER for the given effective / as-of date of 4/10/2021)

 

waeltken_0-1620235824615.png

 

Here is my measure and the output from PBI Desktop:

 

2021-05-05_13-32-20.png

 

 

I appreciate your time in helping point me in the right direction. 

 

Many thanks,

 

 

Henrik

 

1 ACCEPTED SOLUTION

@waeltken,

 

Replace line 21 in your example with the code below:

 

CALCULATE (
   COUNTROWS ( FILTER ( vResult, 'Sample'[Employee Status] = "Active" ) )
)

 

The FILTER function iterates the virtual table vResult, filtering the specified Employee Status.

View solution in original post

10 REPLIES 10
waeltken
Frequent Visitor

@DataInsights 

 

I wanted to follow up and close the loop here - I had some issues adapting the measure you provided to my production date scenario where I actually was going to pull the as-of-data from a calendar table instead of a slicer, but now have hopefully figured it out another way. Here's how I solved for the issue:

 

waeltken_1-1620826896190.png

 

This version filters for Status=Active, Employee Class=Regular and Division<>Global, which I had been filtering via table relationships from lookup table previously but it was reducing the SF_All_Records table before the Max functions could distill the latest records and related dimension field values. 

 

Is there a way to "deactivate" table relationship filter context for the CALCULATETABLE argument part of the SUMX, but then apply the selected filters for Employee Class & Division etc. for the second argument in the SUMX iterator, once the As-of Date table has been successfully distilled? That way, I could remove the additional hard coded filters for those dimensions and place them in the Visual Filters as needed.

 

Also I 

 

Regards,

 

 

Henrik

@waeltken,

 

I've been pondering how to achieve this, and found an article that may be of use. The concept is to use a separate date table that is not subject to filtering from the slicer. You capture PeriodLastDate from the filter context of the matrix row, and evaluate all rows where Event Date <= PeriodLastDate (find the row with the latest date for each user, and if the status is Active then return 1).

 

https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/ 

 

Excerpt:

 

It is worth remembering that the selection of the values to show on the axis is independent from the measure. If a slicer is filtering one month, there is no way to show additional months from the same table on either the rows or the columns (or the X-axis, as in the example). Therefore, we must create a separate table that is not subject to filtering from the slicer. This way, columns from that table show all the rows, and we can control their visibility through a measure.

DataInsights
Super User II
Super User II

@waeltken,

 

Try this solution. There is a relationship between the date table and the Sample table. The date slicer is type Before (up to and including the specified date).

 

Measure:

 

ActiveHeadCount = 
VAR vAsOfDate =
    MAX ( asofdatetable[Date] )
VAR vResult =
    SUMX (
        VALUES ( 'Sample'[User Id] ),
        VAR vUser = 'Sample'[User Id]
        VAR vMaxDate =
            CALCULATE ( MAX ( 'Sample'[Event Date] ), 'Sample'[User Id] = vUser )
        VAR vStatus =
            CALCULATE (
                MAX ( 'Sample'[Employee Status] ),
                'Sample'[User Id] = vUser,
                'Sample'[Event Date] = vMaxDate
            )
        RETURN
            IF ( vStatus = "Active", 1 )
    )
RETURN
    vResult

 

DataInsights_0-1620308908535.png

 

DataInsights_1-1620308921029.png

 

Iterating VALUES ( 'Sample'[User Id] ) gives you the option of displaying User Id in the visual (works in either case). Also, VALUES ( 'Sample'[User Id] ) filters out rows where Event Date is greater than the selected date.

Simplified version:

 

ActiveHeadCount = 
SUMX (
    VALUES ( 'Sample'[User Id] ),
    VAR vUser = 'Sample'[User Id]
    VAR vMaxDate =
        CALCULATE ( MAX ( 'Sample'[Event Date] ), 'Sample'[User Id] = vUser )
    VAR vStatus =
        CALCULATE (
            MAX ( 'Sample'[Employee Status] ),
            'Sample'[User Id] = vUser,
            'Sample'[Event Date] = vMaxDate
        )
    RETURN
        IF ( vStatus = "Active", 1 )
)

 

Thank you so much for the FILTER function, I was elated to try it out in Dax Studio and then building a calculated table in PowerBI. The calculated table doesn't seem to recalc based on effective date parameter, except if I declare a hard value. Tried wiring it up to my [PeriodLastDate] measure in my Calendar table and running a table with many End of Quarters and End Dates but the results are suggesting that the table did not recalc in the different filter context. I read some articles related to calculated tables and the refresh is typically triggered when the model recalculates or the source tables are refreshed. I saw no mention on how to get tables to get refreshed dynimcally from within filter contexts.

I tried also to pass the Effective-Date Filtered Table to CALCULATE(Countrows()) in the context of different vEffDateParameter / PeriodLastDay , but the results don't make sense yet. I will keep trying. 

 

At least I can now recreate the state-of-the-data per user for each effective date so I can somewhat easily create a backup readouts table with limited as-of-dates / snapshot dates. 

 

Thank you,

 

 

Henrik

@waeltken,

 

That's correct--calculated tables and calculated columns don't inherit filter context. The measure [Effective Date Parameter] mimics a slicer/filter, but you have to "hard code" the date in the measure. If you want the FILTER function to inherit filter context, you'll need to put the FILTER function in a virtual table in a measure.

DataInsights, 

 

I've been using this construct now in trying to build out my model for some weeks now and it works fine with limited scope. I am trying to figure out if there isn't another way to create a dynamic filter on my entire source data table which first removes the future dated effective records and then filters out the max dated record available per ID, returning a deduplicated effective-dated table which I can then use in a CALCULATE(COUNTROWS(EffectiveDatedMaxRecordperID, with all my required status flags in the filter statements? With the current measure, I have to declare and separately calculate as a VAR any category/dimension field status at Max Date. In the current measure I also cannot filter using active table relationships as they filter the source data. I was thinking of playing around with creating inactive relationships and then "activating" them later in the evaluation of the expression. For example:
1) would it be possible to calculate a virtual table which filters my master records table by the combined columns of [User ID] & [MaxDate] where All Rercords[Event Date] <= Requested Effective Date (parameter from Date Measure]

 

Regards,

 

 

Henrik

 

 

DataInsights,

 

I have been experimenting with how to pass the vResult table to CALCULATE or or iterator functions like COUNTAX to introduce category filters like e. g. Employee Status = "Active", but when I tried for example 

CALCULATE(COUNTROWS(vResult),SampleData[Employee Status] = "Active")
 
I get the same measure as I get w/o the filter expressiom (CALCULATE(COUNTROWS(vResult)). Of course I cannot put vResult[Employee Status] as it's inside the measure, but isn't there a way to tell DAX to filter the table delivered by vResult. That table has all of the columns originally included in 'Sample Data' as per DAX Studio.
 
waeltken_0-1623262730730.png

In the Sample Data set however, as of 3/26/2021, one of the three employees is Employee Status = Terminated. Looks like the filter on employee status is either not filtering the vResult table or it is filtering the source data before the vResult effective dated deduplicated table output is calculated, essential leaving only the source rows where any employee is Employee Status = "Active" and then Max dating and deduplicating from there. 

 

Is there anyway to coax DAX to futher work on the inherited table inside the measure? I appreciate any additional pointers you have for me.

 

Regards,

 

 

Henrik

@waeltken,

 

Replace line 21 in your example with the code below:

 

CALCULATE (
   COUNTROWS ( FILTER ( vResult, 'Sample'[Employee Status] = "Active" ) )
)

 

The FILTER function iterates the virtual table vResult, filtering the specified Employee Status.

View solution in original post

@waeltken,

 

You could create a calculated table that returns the row with the latest effective date as of the date measure parameter.

 

Measure:

 

Effective Date Parameter = DATE ( 2021, 3, 25)

 

Calculated table:

 

MaxEffectiveDate = 
VAR vEffDateParameter = [Effective Date Parameter]
VAR vResult =
    FILTER (
        'Sample',
        VAR vUser = 'Sample'[User Id]
        VAR vMaxDate =
            CALCULATE (
                MAX ( 'Sample'[Event Date] ),
                ALL ( 'Sample' ),
                'Sample'[User Id] = vUser,
                'Sample'[Event Date] <= vEffDateParameter
            )
        RETURN
            'Sample'[User Id] = vUser
                && 'Sample'[Event Date] = vMaxDate
    )
RETURN
    vResult

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors