Forum Discussion

BryonScruggs's avatar
BryonScruggs
Frequent Visitor
3 years ago
Solved

DAX Variable Scope - Cannot create variables within the correct row context

Hello Internet, 

 

I am working on a problem involving the scope of DAX variables. My code defines the hire date and termination date of employees and them implements logic based on the result. My problem is that the scope of my variable is NOT in the row context of each employee, rather, its shows the minimum hiredate and maximum hiredate for ALL employees. Can someone show me how this could could be modified so that the variables "Hire" and "Term" are set for each individual employee? I suspect an iterator function is missing, but I'm stuck. 

 

thanks!

 

Employment Cases =
VAR PeriodStart =
    DATE ( 2022, 01, 01 )
VAR PeriodEnd =
    DATE ( 2022, 03, 31 )
VAR Hire =
    MIN ( attr_Tkpr_vw[TkprDateHire] ) // How do I define this variable so that it results in each employees hire date?
VAR Term =
    MIN ( attr_Tkpr_vw[TkprDateTerm] ) // How do I define this variable so that it results in each employees term date?
VAR ReportPeriod =
    DATEDIFF ( PeriodStart, PeriodEnd, MONTH )
VAR S3 =
    DATEDIFF ( Hire, PeriodEnd, MONTH ) / ReportPeriod
VAR S4 =
    DATEDIFF ( Hire, Term, MONTH )
RETURN
    SELECTCOLUMNS (
        attr_Tkpr_vw,
        "EmpID", attr_Tkpr_vw[TkprNumber],
        "Hire", attr_Tkpr_vw[TkprDateHire],
        "Term", attr_Tkpr_vw[TkprDateTerm],
        "Hire Test", Hire,
        "Term Test", Term,
        "Employment Period",
            SWITCH (
                TRUE (),
                AND (
                    AND (
                        attr_Tkpr_vw[TkprDateHire] >= PeriodStart,
                        attr_Tkpr_vw[TkprDateHire] <= PeriodEnd
                    ),
                    attr_Tkpr_vw[TkprDateTerm] >= PeriodEnd
                ), S3,
                AND (
                    AND (
                        attr_Tkpr_vw[TkprDateHire] >= PeriodStart,
                        attr_Tkpr_vw[TkprDateHire] <= PeriodEnd
                    ),
                    AND (
                        attr_Tkpr_vw[TkprDateTerm] >= PeriodStart,
                        attr_Tkpr_vw[TkprDateTerm] <= PeriodEnd
                    )
                ), S4
            )
    )
DAX
Variable calculation
  • v-jialluo-msft's avatar
    v-jialluo-msft
    3 years ago

    Hi BryonScruggs ,

     

    Please follow these steps:

    (1) Create a new Table

    Employment Cases = 
VAR PeriodStart =
    DATE ( 2022, 01, 01 )
VAR PeriodEnd =
    DATE ( 2022, 03, 31 )

RETURN
    ADDCOLUMNS (
        SELECTCOLUMNS (
            attr_Tkpr_vw,
            "EmpID", attr_Tkpr_vw[TkprNumber],
            "Hire", attr_Tkpr_vw[TkprDateHire],
            "Term", attr_Tkpr_vw[TkprDateTerm],
            "Hire Test",
                CALCULATE (
                    MIN ( attr_Tkpr_vw[TkprDateHire] ),
                    ALLEXCEPT ( attr_Tkpr_vw, 'attr_Tkpr_vw'[TkprNumber] )
                ),
            "Term Test",
                CALCULATE (
                    MIN ( attr_Tkpr_vw[TkprDateTerm] ),
                    ALLEXCEPT ( attr_Tkpr_vw, 'attr_Tkpr_vw'[TkprNumber] )
                )
        ),
        "Employment Period",
            SWITCH (
                TRUE (),
                AND ( AND ( [Hire] >= PeriodStart, [Hire] <= PeriodEnd ), [Term] >= PeriodEnd ), DATEDIFF ( [Hire Test], PeriodEnd, MONTH ),
                AND (
                    AND ( [Hire] >= PeriodStart, [Hire] <= PeriodEnd ),
                    AND ( [Term] >= PeriodStart, [Term] <= PeriodEnd )
                ), DATEDIFF ( [Hire Test], [Term Test], MONTH )
            )
    )

     

    (2)Final output

     

     

    If that's not what you need, please share the sample file

     

    Best Regards,

    Gallen Luo

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-jialluo-msft's avatar
    v-jialluo-msft
    Community Support
    3 years ago

    Hi BryonScruggs ,

     

    Please follow these steps:

    (1) Create a new Table

    Employment Cases = 
VAR PeriodStart =
    DATE ( 2022, 01, 01 )
VAR PeriodEnd =
    DATE ( 2022, 03, 31 )

RETURN
    ADDCOLUMNS (
        SELECTCOLUMNS (
            attr_Tkpr_vw,
            "EmpID", attr_Tkpr_vw[TkprNumber],
            "Hire", attr_Tkpr_vw[TkprDateHire],
            "Term", attr_Tkpr_vw[TkprDateTerm],
            "Hire Test",
                CALCULATE (
                    MIN ( attr_Tkpr_vw[TkprDateHire] ),
                    ALLEXCEPT ( attr_Tkpr_vw, 'attr_Tkpr_vw'[TkprNumber] )
                ),
            "Term Test",
                CALCULATE (
                    MIN ( attr_Tkpr_vw[TkprDateTerm] ),
                    ALLEXCEPT ( attr_Tkpr_vw, 'attr_Tkpr_vw'[TkprNumber] )
                )
        ),
        "Employment Period",
            SWITCH (
                TRUE (),
                AND ( AND ( [Hire] >= PeriodStart, [Hire] <= PeriodEnd ), [Term] >= PeriodEnd ), DATEDIFF ( [Hire Test], PeriodEnd, MONTH ),
                AND (
                    AND ( [Hire] >= PeriodStart, [Hire] <= PeriodEnd ),
                    AND ( [Term] >= PeriodStart, [Term] <= PeriodEnd )
                ), DATEDIFF ( [Hire Test], [Term Test], MONTH )
            )
    )

     

    (2)Final output

     

     

    If that's not what you need, please share the sample file

     

    Best Regards,

    Gallen Luo

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    Try to reference and define the date variables inside an iterator function which take an employee-relevant column as the first argument. for example,

    ADDCOLUMNS(

    ALL(Employee[EmployeeID]),

    ...

    )

     

    VAR, VAR,...RETURN is just an expression block. A VAR is valid immediately after its definition and till its immdiate next RETURN.