Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Everyone,
New user to powerbi here. I am trying to create a report that when given a list of rooms and a date period, I can show how many times each room was logged as well as the day of the week. My goal is to have my user provide a list of rooms and the date range of the week or 2 week period, and have the report show each room and how many times each room was completed as well as the day of the week.
My Data looks like this
HospitalB
SessionID | Uploaddate | SessionType | Room | Technician | StartTime | EndTime | StopReason | Runtime | Device | Version | Account | |||
847719 | 1/1/17 12:05 AM | Spore | OR1 | dossing | 12/31/16 9:13 PM | 12/31/16 10:05 PM | Cycle Complete | 3133 | 304 | 1.9.2A | HospitalA | |||
847720 | 1/1/17 12:07 AM | Bacteria | OR1 | dee | 12/31/16 10:33 PM | 12/31/16 11:07 PM | Cycle Complete | 1985 | 51 | 1.9.2A |
| |||
847721 | 1/1/17 12:08 AM | Spore | OR1 | manuel | 12/31/16 8:36 PM | 12/31/16 9:08 PM | Cycle Complete | 1916 | 304 | 1.9.2A |
| |||
847723 | 1/1/17 12:19 AM | Bacteria | OR3 | cindy | 12/31/16 11:04 PM | 12/31/16 11:19 PM | Cycle Complete | 904 | 386 | 1.9.2A | HospitalA | |||
Solved! Go to Solution.
Hi @Cyprix,
If I understand you correctly, you should be able to firstly use the formula below to add a calculate column to get the day of the week.
Day of Week = FORMAT(HospitalB[EndTime],"dddd")
Then use the formula below to create a new measure to calculate how many times each room was completed.
Count Of Completed = CALCULATE(COUNTA(HospitalB[StopReason]),HospitalB[StopReason]="Cycle Complete")
And then you can show the Room column with the Day of Week column and the measure [Count Of Completed] on the Table visual.
Regards
Hi @Cyprix,
If I understand you correctly, you should be able to firstly use the formula below to add a calculate column to get the day of the week.
Day of Week = FORMAT(HospitalB[EndTime],"dddd")
Then use the formula below to create a new measure to calculate how many times each room was completed.
Count Of Completed = CALCULATE(COUNTA(HospitalB[StopReason]),HospitalB[StopReason]="Cycle Complete")
And then you can show the Room column with the Day of Week column and the measure [Count Of Completed] on the Table visual.
Regards
Yes thank you that helped alot but now I am trying to have my tables show blanks for days that no rooms were not logged.
The table on the left shows data where no rooms were logged on Monday, and I feel the data can be easily misunderstood if it does not show the blank Monday column on the table. The goal is to have table 1 one look like table 2 wether or not a day was skipped.
Thank you in advance,
Alex
Hi @Cyprix,
A few more steps are needed to achieve that.
1. Use the formula below to create a new table which contains a column of Day of Week.
WeekTable = DATATABLE ( "Week Day", STRING, "Index", INTEGER, { { "Sunday", 1 }, { "Monday", 2 }, { "Tuesday", 3 }, { "Wednesday", 4 }, { "Thursday", 5 }, { "Friday", 6 }, { "Saturday", 7 } } )
2. Sort Day of Week column by Index column.
3. Create a relationship between the WeekTable and your original table with the Day of Week column.
4. Then show the Day of Week column from the WeekTable(instead of the original table) on the Matrix, and right click on the column and select "Show items with no data".
Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
20 | |
19 | |
18 | |
9 |