DAX Variable Scope-CodePudding

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.

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
            )
     
   )

I suspect an iterator function is missing. I tried enclosing the SELECTCOLUMNS expression with CALCULATETABLE. This did not work.

The result set shows that in each case the Hire and Term dates are calculated for the entire table, not each individual.

enter image description here

CodePudding user response：

You need to move the variable scope to inside the SELECTCOLUMNS:

    SELECTCOLUMNS (
        attr_Tkpr_vw,
        "EmpID", attr_Tkpr_vw[TkprNumber],
        "Hire", attr_Tkpr_vw[TkprDateHire],
        "Term", attr_Tkpr_vw[TkprDateTerm],
        "Hire Test", VAR Hire = attr_Tkpr_vw[TkprDateHire]
                     RETURN Hire,

If you want to do MIN functions then I believe you need a CALCULATE statement to transition the row context to a filter context:

    SELECTCOLUMNS (
        attr_Tkpr_vw,
        "EmpID", attr_Tkpr_vw[TkprNumber],
        "Hire", attr_Tkpr_vw[TkprDateHire],
        "Term", attr_Tkpr_vw[TkprDateTerm],
        "Hire Test", VAR Hire = CALCULATE(MIN(attr_Tkpr_vw[TkprDateHire]))
                     RETURN Hire,

CodePudding user response：

Move your variables into SELECTCOLUMNS scope (it's an iterator). You can also simplify your code:

Employment Cases =
SELECTCOLUMNS (
    attr_Tkpr_vw,
    "EmpID", attr_Tkpr_vw[TkprNumber],
    "Hire", attr_Tkpr_vw[TkprDateHire],
    "Term", attr_Tkpr_vw[TkprDateTerm],
    "Employment Period",
        VAR PeriodStart = DATE ( 2022, 01, 01 )
        VAR PeriodEnd = DATE ( 2022, 03, 31 )
        VAR ReportPeriod = DATEDIFF ( PeriodStart, PeriodEnd, MONTH )
        VAR S3 = DATEDIFF ( attr_Tkpr_vw[TkprDateHire], PeriodEnd, MONTH ) / ReportPeriod
        VAR S4 = DATEDIFF ( attr_Tkpr_vw[TkprDateHire], attr_Tkpr_vw[TkprDateTerm], MONTH )
        VAR ValidHire = ttr_Tkpr_vw[TkprDateHire] >= PeriodStart
            && attr_Tkpr_vw[TkprDateHire] <= PeriodEnd
        RETURN
            SWITCH (
                TRUE (),
                ValidHire 
                    && attr_Tkpr_vw[TkprDateTerm] >= PeriodEnd, S3,
                ValidHire
                    && attr_Tkpr_vw[TkprDateTerm] >= PeriodStart
                    && attr_Tkpr_vw[TkprDateTerm] <= PeriodEnd, S4
            )
)