Dax Logic Issue virtual Table

Hi nagendranath,

Thank you for reaching out to Microsoft Fabric Community.

Thank you Shai_Karmani for the prompt response.

Thank you for sharing the updated measure. Here looks like the issue is not related to the total row names or trailing spaces, here the KEEPFILTERS(ActiveEmpCodes) is not properly applying the active employee filter back onto GetEmployeeMaster1[Code].

Because of this the total rows are getting incorrect values or returning blank.

• Please try replacing "KEEPFILTERS(ActiveEmpCodes)" with:
  "TREATAS(ActiveCodes, GetEmployeeMaster1[Code])"

Apply the same change to all the count variables. This should make the total rows correctly respect the active employee list and return the expected values.

Thanks and regards,

Anjan Kumar Chippa

Its possible that some filters from the visual are still impacting the measure. Try removing the filters on the employee master table as well as the mapping table.

Onroll EXCLUDING Long absent_mmmm =
VAR CurrentRow =
    SELECTEDVALUE ( MappingTable[Nature of Work] )
VAR SelectedDate =
    MAX ( Getattendance_may[Date] ) -- :white_heavy_check_mark: Build INACTIVE employee code list from attendance
VAR InactiveEmpCodes =
    SELECTCOLUMNS (
        FILTER (
            ALL ( Getattendance_may[EmployeeCode] ),
            VAR Emp = Getattendance_may[EmployeeCode]
            VAR LastPresentDate =
                CALCULATE (
                    MAX ( Getattendance_may[Date] ),
                    FILTER (
                        ALL ( Getattendance_may ),
                        Getattendance_may[EmployeeCode] = Emp && Getattendance_may[Status] = "Present"
                            && Getattendance_may[Date] <= SelectedDate
                    )
                )
            VAR TrailingAbsentCount =
                CALCULATE (
                    COUNTROWS ( Getattendance_may ),
                    FILTER (
                        ALL ( Getattendance_may ),
                        Getattendance_may[EmployeeCode] = Emp && Getattendance_may[Status] = "Absent"
                            && Getattendance_may[Date] > LastPresentDate
                            && Getattendance_may[Date] <= SelectedDate
                    )
                )
            RETURN
                TrailingAbsentCount >= 8
        ),
        "Code", Getattendance_may[EmployeeCode]
    ) -- :white_heavy_check_mark: Active EmpMaster = All EmpMaster codes MINUS inactive ones
VAR AllMasterCodes =
    SELECTCOLUMNS ( ALL ( GetEmployeeMaster1 ), "Code", GetEmployeeMaster1[Code] )
VAR ActiveMasterCodes =
    EXCEPT ( AllMasterCodes, InactiveEmpCodes ) -- :white_heavy_check_mark: Mirror Including measure exactly, just filter EmpMaster to ActiveMasterCodes
VAR TotalMachinist =
    CALCULATE (
        COUNTROWS ( GetEmployeeMaster1 ),
        REMOVEFILTERS ( MappingTable ),
        REMOVEFILTERS ( GetEmployeeMaster1 ),
        GetEmployeeMaster1[SUBC] IN { "MACHINIST", "SMS (MACHINIST)" },
        TREATAS ( ActiveMasterCodes, GetEmployeeMaster1[Code] )
    )
VAR TotalInclTrainee =
    CALCULATE (
        COUNTROWS ( GetEmployeeMaster1 ),
        REMOVEFILTERS ( MappingTable ),
        REMOVEFILTERS ( GetEmployeeMaster1 ),
        GetEmployeeMaster1[SUBC]
            IN { "MACHINIST", "SMS (MACHINIST)", "TRAINEE MACHINIST" },
        TREATAS ( ActiveMasterCodes, GetEmployeeMaster1[Code] )
    )
VAR TotalDM_DNM =
    CALCULATE (
        COUNTROWS ( GetEmployeeMaster1 ),
        REMOVEFILTERS ( MappingTable ),
        REMOVEFILTERS ( GetEmployeeMaster1 ),
        GetEmployeeMaster1[SUBC]
            IN {
                "MACHINIST",
                "SMS (MACHINIST)",
                "TRAINEE MACHINIST",
                "NON MACHINIST",
                "SMS ( NON-MACHINIST)"
            },
        TREATAS ( ActiveMasterCodes, GetEmployeeMaster1[Code] )
    )
VAR TotalFactory =
    CALCULATE (
        COUNTROWS ( GetEmployeeMaster1 ),
        REMOVEFILTERS ( MappingTable ),
        REMOVEFILTERS ( GetEmployeeMaster1 ),
        GetEmployeeMaster1[SUBC]
            IN {
                "MACHINIST",
                "SMS (MACHINIST)",
                "TRAINEE MACHINIST",
                "NON MACHINIST",
                "SMS ( NON-MACHINIST)",
                "FACTORY STAFF"
            },
        TREATAS ( ActiveMasterCodes, GetEmployeeMaster1[Code] )
    )
VAR NormalCount =
    CALCULATE (
        COUNTROWS ( GetEmployeeMaster1 ),
        REMOVEFILTERS ( MappingTable ),
        REMOVEFILTERS ( GetEmployeeMaster1 ),
        GetEmployeeMaster1[SUBC] = CurrentRow,
        TREATAS ( ActiveMasterCodes, GetEmployeeMaster1[Code] )
    )
RETURN
    SWITCH (
        TRUE (),
        CurrentRow = "Total Machinist", TotalMachinist,
        CurrentRow = "Total Machinist Incl Trainee", TotalInclTrainee,
        CurrentRow = "TOTAL DM + DNM", TotalDM_DNM,
        CurrentRow = "Total Factory", TotalFactory,
        NormalCount
    )