Forum Discussion

Cyprix's avatar
Cyprix
Frequent Visitor
8 years ago
Solved

Data Relationship help

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 th...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    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. :smileyhappy:

     

    Regards

Cyprix's avatar
Cyprix
Frequent Visitor
8 years ago

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's avatar
v-ljerr-msft
Microsoft Employee
8 years ago

Hi Cyprix,

 

A few more steps are needed to achieve that. :smileyhappy:

 

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