Data Relationship help

[Cyprix]

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

HospitalB

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

HospitalA

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

[v-ljerr-msft · Accepted 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

[Cyprix]

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

[v-ljerr-msft]

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