Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

A new Data Days event is coming soon! This time we’re going bigger than ever. Fabric, Power BI, SQL, AI and more. Don't miss out.

Reply
nagendranath
Frequent Visitor

Dax Logic Issue virtual Table

Saturday

Hey, I need help with an issue in my Power BI measure.

I’m using a measure that creates a virtual table, filters only the active employees, and then performs the count based on each selected value. I also have a template/mapping table that includes a “Total” row.

In the measure, for the Total condition, I specified that it should include records where SUBC is in ("cat1", "cat2"). However, when I drag the measure into the visual, the Total value is not working and is showing as blank.

Could you please help me identify what might be causing this issue?

Please find the dax measure & the matrix visual which i am using.



Onroll EXCLUDING Long absent_mmmm =
VAR CurrentRow = SELECTEDVALUE(MappingTable[Nature of Work])
VAR SelectedDate = MAX(Getattendance_may[Date])

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

-- ✅ Active EmpMaster = All EmpMaster codes MINUS inactive ones
VAR AllMasterCodes =
    SELECTCOLUMNS(
        ALL(GetEmployeeMaster1),
        "Code", GetEmployeeMaster1[Code]
    )

VAR ActiveMasterCodes =
    EXCEPT(AllMasterCodes, InactiveEmpCodes)

-- ✅ Mirror Including measure exactly, just filter EmpMaster to ActiveMasterCodes
VAR TotalMachinist =
    CALCULATE(
        COUNTROWS(GetEmployeeMaster1),
        ALL(MappingTable),
        GetEmployeeMaster1[SUBC] IN { "MACHINIST", "SMS (MACHINIST)" },
        TREATAS(ActiveMasterCodes, GetEmployeeMaster1[Code])
    )

VAR TotalInclTrainee =
    CALCULATE(
        COUNTROWS(GetEmployeeMaster1),
        ALL(MappingTable),
        GetEmployeeMaster1[SUBC] IN { "MACHINIST", "SMS (MACHINIST)", "TRAINEE MACHINIST" },
        TREATAS(ActiveMasterCodes, GetEmployeeMaster1[Code])
    )

VAR TotalDM_DNM =
    CALCULATE(
        COUNTROWS(GetEmployeeMaster1),
        ALL(MappingTable),
        GetEmployeeMaster1[SUBC] IN {
            "MACHINIST", "SMS (MACHINIST)", "TRAINEE MACHINIST",
            "NON MACHINIST", "SMS ( NON-MACHINIST)"
        },
        TREATAS(ActiveMasterCodes, GetEmployeeMaster1[Code])
    )

VAR TotalFactory =
    CALCULATE(
        COUNTROWS(GetEmployeeMaster1),
        ALL(MappingTable),
        GetEmployeeMaster1[SUBC] IN {
            "MACHINIST", "SMS (MACHINIST)", "TRAINEE MACHINIST",
            "NON MACHINIST", "SMS ( NON-MACHINIST)", "FACTORY STAFF"
        },
        TREATAS(ActiveMasterCodes, GetEmployeeMaster1[Code])
    )

VAR NormalCount =
    CALCULATE(
        COUNTROWS(GetEmployeeMaster1),
        ALL(MappingTable),
        GetEmployeeMaster1[SUBC] = CurrentRow,
        TREATAS(ActiveMasterCodes, GetEmployeeMaster1[Code])
    )

RETURN
    IF(
        CurrentRow = "Total Machinist", TotalMachinist,
        IF(
            CurrentRow = "Total Machinist Incl Trainee", TotalInclTrainee,
            IF(
                CurrentRow = "TOTAL DM + DNM", TotalDM_DNM,
                IF(
                    CurrentRow = "Total Factory", TotalFactory,
                    NormalCount
                )
            )
        )
    )

nagendranath_0-1779520663243.png

 

 

 

Labels:
Message 1 of 6
266 Views
0
5 REPLIES 5
johnt75
Super User
Super User

Tuesday

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
    )
Message 6 of 6
33 Views
0
nagendranath
Frequent Visitor

Tuesday
Onroll EXCLUDING Long absent7 =
VAR CurrentRow =
    SELECTEDVALUE(MappingTable[Nature of Work])

 

VAR SelectedDate =
    MAX(DateTable[Date])

 

/* ACTIVE EMPLOYEE LIST */

 

VAR ActiveEmpCodes =

 

    FILTER(
        VALUES(GetEmployeeMaster1[Code]),

 

        VAR Emp =
            GetEmployeeMaster1[Code]

 

        VAR LastPresentDate =

 

            CALCULATE(
                MAX(Getattendance_may[Date]),

 

                FILTER(
                    ALL(Getattendance_may),

 

                    Getattendance_may[EmployeeCode] = Emp
                        &&
                    Getattendance_may[Status] = "Present"
                        &&
                    Getattendance_may[Date] <= SelectedDate
                )
            )

 

        RETURN
            NOT(ISBLANK(LastPresentDate))
                &&
            DATEDIFF(
                LastPresentDate,
                SelectedDate,
                DAY
            ) <= 8
    )

 

/* DETAIL COUNTS */

 

VAR CountMACHINIST =

 

    CALCULATE(
        DISTINCTCOUNT(GetEmployeeMaster1[Code]),

 

        ALL(MappingTable),

 

        GetEmployeeMaster1[SUBC] = "MACHINIST",

 

        KEEPFILTERS(ActiveEmpCodes)
    )

 

VAR CountSMS_MACHINIST =

 

    CALCULATE(
        DISTINCTCOUNT(GetEmployeeMaster1[Code]),

 

        ALL(MappingTable),

 

        GetEmployeeMaster1[SUBC] = "SMS (MACHINIST)",

 

        KEEPFILTERS(ActiveEmpCodes)
    )

 

VAR CountTRAINEE_MACHINIST =

 

    CALCULATE(
        DISTINCTCOUNT(GetEmployeeMaster1[Code]),

 

        ALL(MappingTable),

 

        GetEmployeeMaster1[SUBC] = "TRAINEE MACHINIST",

 

        KEEPFILTERS(ActiveEmpCodes)
    )

 

VAR CountNON_MACHINIST =

 

    CALCULATE(
        DISTINCTCOUNT(GetEmployeeMaster1[Code]),

 

        ALL(MappingTable),

 

        GetEmployeeMaster1[SUBC] = "NON MACHINIST",

 

        KEEPFILTERS(ActiveEmpCodes)
    )

 

VAR CountSMS_NON_MACHINIST =

 

    CALCULATE(
        DISTINCTCOUNT(GetEmployeeMaster1[Code]),

 

        ALL(MappingTable),

 

        GetEmployeeMaster1[SUBC] = "SMS ( NON-MACHINIST)",

 

        KEEPFILTERS(ActiveEmpCodes)
    )

 

VAR CountFACTORY_STAFF =

 

    CALCULATE(
        DISTINCTCOUNT(GetEmployeeMaster1[Code]),

 

        ALL(MappingTable),

 

        GetEmployeeMaster1[SUBC] = "FACTORY STAFF",

 

        KEEPFILTERS(ActiveEmpCodes)
    )

 

/* TOTALS */

 

VAR TotalMachinist =
    CountMACHINIST + CountSMS_MACHINIST

 

VAR TotalInclTrainee =
    CountMACHINIST
        + CountSMS_MACHINIST
        + CountTRAINEE_MACHINIST

 

VAR TotalDM_DNM =
    CountMACHINIST
        + CountSMS_MACHINIST
        + CountTRAINEE_MACHINIST
        + CountNON_MACHINIST
        + CountSMS_NON_MACHINIST

 

VAR TotalFactory =
    CountMACHINIST
        + CountSMS_MACHINIST
        + CountTRAINEE_MACHINIST
        + CountNON_MACHINIST
        + CountSMS_NON_MACHINIST
        + CountFACTORY_STAFF

 

/* DETAIL ROW */

 

VAR NormalCount =

 

    CALCULATE(
        DISTINCTCOUNT(GetEmployeeMaster1[Code]),

 

        ALL(MappingTable),

 

        GetEmployeeMaster1[SUBC] = CurrentRow,

 

        KEEPFILTERS(ActiveEmpCodes)
    )

 

RETURN

 

    SWITCH(
        TRUE(),

 

        CurrentRow = "Total Machinist",
            TotalMachinist,

 

        CurrentRow = "Total Machinist Incl Trainee",
            TotalInclTrainee,

 

        CurrentRow = "TOTAL DM + DNM",
            TotalDM_DNM,

 

        CurrentRow = "Total Factory",
            TotalFactory,

 

        NormalCount
    )

I’m trying to implement this measure, but the total grid is showing blank values. I’ve been stuck on this issue for the past week.

Could anyone please help me with this issue?

Message 4 of 6
51 Views
0
v-achippa
Community Support
Community Support
In response to nagendranath

Tuesday

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

Message 5 of 6
33 Views
0
nagendranath
Frequent Visitor

Sunday

Hi @Shai_Karmani 

Thanks for the reply. I don’t think the issue is related to naming conventions or trailing spaces, as I have already verified those.

nagendranath_0-1779682968371.png

 

As shown in the screenshot, the total should be calculated as 1732 + 47 = 1779. However, the current total is incorrect because it appears to be considering all records from the attendance table.



 

Message 3 of 6
101 Views
0
Shai_Karmani
Solution Specialist
Solution Specialist

Saturday

Hey nagendranath,

I think the screenshot actually narrows it down nicely. Your normal rows work in the EXCLUDING column (1685, 48...) and your Total rows work in the INCLUDING column, so the virtual table and TREATAS are both fine.

if the TotalMachinist branch were really being hit, it couldn't return blank. "MACHINIST" alone already gives 1685, so an IN filter can't collapse to nothing. Blank only happens when the Total rows fall through to NormalCount, which runs SUBC = "Total Machinist". No employee has that SUBC, so you get blank.

So the IF conditions in your EXCLUDING measure aren't matching CurrentRow, even though the same strings match in your Including measure. Usually that's an invisible diff: a trailing space, a non-breaking space, or the two IF blocks just aren't identical.

Two quick ways to find it:

  1. Temporarily change your RETURN to just RETURN CurrentRow, then drop the measure in the matrix. It'll show you the exact text each Total row is sending in. Compare that to what you typed in your IF, they probably don't match perfectly (a stray space, different casing, something small).

  2. Or just open both measures side by side and read the Total lines slowly. The working one and the blank one should be identical, so wherever they differ is your culprit.

The first one is faster, so I'd start there.

If this helped, a thumbs up and accepting the solution would be appreciated.

 

Best,
Shai Karmani

Let's connect in LinkedIn

Message 2 of 6
239 Views

Helpful resources

Announcements
May Power BI Update Carousel

Power BI Monthly Update - May 2026

Check out the May 2026 Power BI update to learn about new features.

Fabric SQL PBI Data Days

Data Days 2026 coming soon!

Sign up to receive a private message when registration opens and key events begin.

New to Fabric survey Carousel

New to Fabric Survey

If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

View All