Forum Discussion
coding7
1 year agoFrequent Visitor
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...
- 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
1 year agoNot applicable
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
1 year agoNot applicable
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.