Forum Discussion

coding7's avatar
coding7
Frequent Visitor
1 year ago
Solved

Manipulate the total in a matrix

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 !

Need Help
  • coding7's avatar
    coding7
    1 year ago

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

     

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      coding7
      Frequent Visitor
      1 year ago

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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.