Need help tracking activities (Filter, Count, Conditional formatting)

[Marco_88]

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 

[Ritaf1983 · Accepted solution]

Hi @Marco_88 
You can use 2 measures:
1. 

Complete rooms =

VAR RoomStatusCounts = SUMMARIZE('database', 'database'[ROOM NUMBER], "StatusCount", DISTINCTCOUNT('database'[STATUS]))

RETURN

COUNTROWS(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

[Marco_88]

Thank you very much!
I still have a long way to go!

Best 

Marco 

[Ritaf1983]

@Marco_88 
You're welcome 🙂
And...it is going better with practice 🙂