Forum Discussion

bwarner87's avatar
bwarner87
Advocate I
4 years ago
Solved

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

    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.

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

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.

  • bwarner87's avatar
    bwarner87
    Advocate I
    4 years ago

    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!