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

Calculated Table containing Filter Expression with date lookup

DAX Experts,

 

I’m hoping you can help. I got to a good point for calculating time in position in my Master Employee Table for each employee row but then, some new requirements came in and I’m stuck.

 

I was taking the max date value for a column from a calculated table to use in a date difference calculation to get time in position filtering out certain transactions that matched a date. See below working calculation.

 

Time in Position = DATEDIFF(

    MAXX(

        CalculateTable('Salary History (All)', 'Salary History (All)'[Appointment Start Date] <>  dt"2020-09-01")

        , 'Salary History (All)'[Appointment Start Date]),

        TODAY(),MONTH)

        /12

 

My filter expression within the calculated table was simple and excluding all transactions with a certain hard coded date “dt"2020-09-01"”.

 

However, now I need to exclude all transaction rows where the Appointment Start Date = the Appointment Start Date for any row with “Salary Reason Code” column = JRC or JAC.

 

I tried to illustrate in the excel table below the data scenario in the “Salary History (All)” table and the “Master Employee Table” . The blue (transactions matching the JRC Appointment Start Date) and the purple (transactions matching the JAC Appointment Start Date)transactions represent transactions that should get removed with the correct filter expression; this is the part in the formula I’m stuck.

 

I then know how to take the max date of the “Appointment State Date” column to calculate date difference representing time in position for each employee row. In our example the max date of the transactions remaining is 10/1/2014 and then the YOS = 7.00 Years which you see in the master table for the employee with the same employee ID. 

 

Dates are in MM/DD/YYYY format

 

Salary History (All) Table

 

Employee IDSalary Reason CodeAppointment Start Date
123456 07/01/2007
123456 07/01/2007
123456 09/28/2007
123456 09/28/2007
123456ANN09/28/2007
123456ANN09/22/2009
123456JRC09/22/2009
123456DPT09/22/2009
123456DPT09/22/2009
123456ANN09/22/2009
123456FTH01/05/2012
123456ORG01/05/2012
123456MER01/05/2012
123456ANN01/05/2012
123456MER10/01/2014
123456ANN10/01/2014
123456MER10/01/2014
123456ANN10/01/2014
123456ANN10/01/2014
123456MER10/01/2014
123456ANN10/01/2014
123456AST10/01/2014
123456MER09/01/2020
123456JAC09/01/2020
123456ORG09/01/2020
123456MER09/01/2020

 

Master Employee Table

 

Employee IDTime in Position
1234567.00
  

Appreciate any time or suggestions!

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

How about this?

 

Time in Position =
VAR DatesJXC =
    CALCULATETABLE (
        VALUES ( 'Salary History (All)'[Appointment Start Date] ),
        'Salary History (All)'[Salary Reason Code] IN { "JRC", "JAC" }
    )
RETURN
    DATEDIFF (
        CALCULATE (
            MAX ( 'Salary History (All)'[Appointment Start Date] ),
            NOT ( 'Salary History (All)'[Appointment Start Date] IN DatesJXC )
        ),
        TODAY (),
        MONTH
    ) / 12

 

This gives 6.92 but you can add 1 to the DATEDIFF before dividing by 12 to get 7.00.

View solution in original post

3 REPLIES 3
AlexisOlson
Super User
Super User

How about this?

 

Time in Position =
VAR DatesJXC =
    CALCULATETABLE (
        VALUES ( 'Salary History (All)'[Appointment Start Date] ),
        'Salary History (All)'[Salary Reason Code] IN { "JRC", "JAC" }
    )
RETURN
    DATEDIFF (
        CALCULATE (
            MAX ( 'Salary History (All)'[Appointment Start Date] ),
            NOT ( 'Salary History (All)'[Appointment Start Date] IN DatesJXC )
        ),
        TODAY (),
        MONTH
    ) / 12

 

This gives 6.92 but you can add 1 to the DATEDIFF before dividing by 12 to get 7.00.

View solution in original post

It works! I checked a few examples I had that were different use cases. 

 

So that I can learn from this I was reading DAX documentation tied to your solution in order to express it in words. Not sure I quite fully understand it.  I think what you did is create a variable "DatesJXC" to hold a single column table of appointment start date that represented only the distinct dates tied to JRC and JAC salary reason code. This is the list of dates I want to use to exclude transactions. I guess what was odd to me here was when reading the DAX details is that you were able to filter a single column table by another column in the same table which i wouldn't have understood possible.

 

You then moved to the main expression and used caculate to first start with the expression of getting the max date but the key was your filter in the second argument of the calculate function. The use of NOT and IN are new to me but odd in that NOT returns a boolean expression and IN which creates a logical OR condition between each row being compared to a table. so it's like you you gave a false value to each row in the salary history table that had the dates in the variable list of values you created. 

 

I really appreciate this and I think is opening up my understanding a little bit more.

 

Thanks!

It sounds like you understand it properly.

 

Using CALCULATE or CALCULATETABLE with a boolean filter involving a column other than the one(s) used in the first argument is pretty common. Here's some suggested reading if you want more info:

 

https://www.sqlbi.com/blog/marco/2010/01/03/how-calculate-works-in-dax/

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.