Manipulate the total in a matrix

[coding7]

Hey all,

I’m trying to create a matrix that shows the days employees go to the office. To count this, I’m using this DAX measure:

# Days in Office = 
VAR _CurrentDate = SELECTEDVALUE('DimDate'[Datekey])
VAR _CurrentDateP = SELECTEDVALUE('DimDate'[Date])

VAR _IsGoing =
SUMX(
SUMMARIZE(
'FactEmployeePresence',
'FactEmployeePresence'[EvDateTime],
'FactEmployeePresence'[User]
),
DISTINCTCOUNT('FactEmployeePresence'[User])
)

VAR _IsHoliday =
CALCULATE(
COUNTROWS('Public Holidays'),
'Public Holidays'[Date] = _CurrentDateP
) > 0

VAR _IsOnVacation =
CALCULATE(
COUNTROWS('FactTimeResources'),
KEEPFILTERS('FactTimeResources'[Timesheet Code] = "141334"),
'FactTimeResources'[Date Key] = _CurrentDate
) > 0

RETURN
SWITCH(
TRUE(),
NOT(ISBLANK(_IsGoing)) && _IsGoing > 0, 1,
_IsOnVacation, 4,
_IsHoliday, 3,
0
)

It works well, it counts vacation days (value 4), holidays (value 2), days the employee went to the office (value 1), and days they didn’t go (value 0). For example, for this employee, they had two weeks of vacation (4 — gray icon), then the following week they went 4 times (green icon), but the total shows 1, and I wanted it to show 4, because that’s the total number of days they went that week.For the last week, it should show 2, because they went 2 times:

Can someone help please?

Thanks a lot !

[v-tejrama]

Hi @coding7 ,

Thanks for reaching out to the Microsoft fabric community forum.

Has your issue been resolved?If the response provided by @bhanu_gautam   addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone. 

Best Regards,
Tejaswi.
Community Support

[v-tejrama]

Hi @coding7,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

Thank you.

[bhanu_gautam]

@coding7 Try using

DAX
# Days in Office =
VAR _CurrentDate = SELECTEDVALUE('DimDate'[Datekey])
VAR _CurrentDateP = SELECTEDVALUE('DimDate'[Date])

VAR _IsGoing =
SUMX(
SUMMARIZE(
'FactEmployeePresence',
'FactEmployeePresence'[EvDateTime],
'FactEmployeePresence'[User]
),
DISTINCTCOUNT('FactEmployeePresence'[User])
)

VAR _IsHoliday =
CALCULATE(
COUNTROWS('Public Holidays'),
'Public Holidays'[Date] = _CurrentDateP
) > 0

VAR _IsOnVacation =
CALCULATE(
COUNTROWS('FactTimeResources'),
KEEPFILTERS('FactTimeResources'[Timesheet Code] = "141334"),
'FactTimeResources'[Date Key] = _CurrentDate
) > 0

VAR _DailyStatus =
SWITCH(
TRUE(),
NOT(ISBLANK(_IsGoing)) && _IsGoing > 0, 1,
_IsOnVacation, 4,
_IsHoliday, 3,
0
)

RETURN
CALCULATE(
SUMX(
VALUES('DimDate'[Datekey]),
_DailyStatus
),
ALLEXCEPT('DimDate', 'DimDate'[WeekNumber])
)

[coding7 · Accepted solution]

Thanks a lot for your response @bhanu_gautam. I tried your measure, but it didn’t give me the correct results. However, I managed to get it working with this measure:

# Days in Office = 
VAR _CurrentDate =
    SELECTEDVALUE ( 'DimDate'[Datekey] )
VAR _CurrentDateP =
    SELECTEDVALUE ( 'DimDate'[Date] )
VAR _IsGoing =
    CALCULATE (
        DISTINCTCOUNT ( 'FactEmployeePresence'[User] ),
        'FactEmployeePresence'[EvDateTime] = _CurrentDateP
    )
VAR _IsHoliday =
    CALCULATE (
        COUNTROWS ( 'Public Holidays' ),
        'Public Holidays'[Date] = _CurrentDateP
    ) > 0
VAR _IsOnVacation =
    CALCULATE (
        COUNTROWS ( 'FactTimeResources' ),
        'FactTimeResources'[Timesheet Code] = "141334",
        'FactTimeResources'[Date Key] = _CurrentDate
    ) > 0
VAR _DailyStatus =
    SWITCH (
        TRUE (),
        NOT ( ISBLANK ( _IsGoing ) )
            && _IsGoing > 0, 1,
        _IsOnVacation, 4,
        _IsHoliday, 3,
        0
    )
VAR _SumOfPresenceDays =
    CALCULATE (
        COUNTROWS (
            FILTER (
                ADDCOLUMNS (
                    VALUES ( 'DimDate'[Datekey] ),
                    "Presence",
                        VAR LocalDate = 'DimDate'[Datekey]
                        VAR LocalDateP =
                            CALCULATE ( SELECTEDVALUE ( 'DimDate'[Date] ), 'DimDate'[Datekey] = LocalDate )
                        VAR LocalIsGoing =
                            CALCULATE (
                                DISTINCTCOUNT ( 'FactEmployeePresence'[User] ),
                                'FactEmployeePresence'[EvDateTime] = LocalDateP
                            )
                        RETURN
                            IF ( NOT ( ISBLANK ( LocalIsGoing ) ) && LocalIsGoing > 0, 1, BLANK () )
                ),
                [Presence] = 1
            )
        ),
        REMOVEFILTERS ( 'DimDate'[Day] )
    )
RETURN
    IF (
        ISBLANK (
            IF ( HASONEVALUE ( 'DimDate'[Day] ), _DailyStatus, _SumOfPresenceDays )
        ),
        0,
        IF ( HASONEVALUE ( 'DimDate'[Day] ), _DailyStatus, _SumOfPresenceDays )
    )