Forum Discussion
Need help tracking activities (Filter, Count, Conditional formatting)
Hi everyone,
I have a table that tracks the activities of a hotel refurbishmnet.
I have a series of rows corresponding to different activities, done or still to be done, for some rooms.
I would need to see wich rooms (and how many) have completed all activities, and which ones still remain with work in progress.
Attach you can find a XLS file with ROOM NUMBER and STATUS columns.
Attached also a little PBIX works on XLS data.
https://drive.google.com/drive/folders/1wxnGcL2OB53ckLdZPglKHihb1_8KBKys?usp=sharing
For exaple: ROOM n° 1087 has all status DONE so it is complete, Room n° 1088 still has one task to complete (STATUS TO DO).
Can you help me?
Thank you
Best regards
Marco
Hi Marco_88
You can use 2 measures:
1.Complete rooms =VAR RoomStatusCounts = SUMMARIZE('database', 'database'[ROOM NUMBER], "StatusCount", DISTINCTCOUNT('database'[STATUS]))RETURNCOUNTROWS(FILTER(RoomStatusCounts, [StatusCount] = 1 && CONTAINSSTRING(CONCATENATEX(FILTER('database', 'database'[STATUS] = "DONE"), 'database'[ROOM NUMBER], ", "), 'database'[ROOM NUMBER])))2.
Not_completed rooms = DISTINCTCOUNT(DATABASE[ROOM NUMBER])-[Complete rooms]PBIX is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
3 Replies
Hi Marco_88
You can use 2 measures:
1.Complete rooms =VAR RoomStatusCounts = SUMMARIZE('database', 'database'[ROOM NUMBER], "StatusCount", DISTINCTCOUNT('database'[STATUS]))RETURNCOUNTROWS(FILTER(RoomStatusCounts, [StatusCount] = 1 && CONTAINSSTRING(CONCATENATEX(FILTER('database', 'database'[STATUS] = "DONE"), 'database'[ROOM NUMBER], ", "), 'database'[ROOM NUMBER])))2.
Not_completed rooms = DISTINCTCOUNT(DATABASE[ROOM NUMBER])-[Complete rooms]PBIX is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
