Forum Discussion
Manipulate the total in a matrix
- 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 ) )
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])
)
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 )
)