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'[D...
  • 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 )
        )