Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have a filter from a table called day_of_week the table contains 2 columns:
(Whole Number) (string)
staff_day_of_week week_day
1 Sunday
2 Monday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday
I'm setting up a filter that allows the user to select any different combinations of day of the week.
I need to calculate for any giving month or year the total of the sum of all days that are select.
For example in March of 2016 the total Sunday's in the month are 4 however, the total Tuesday's equal 5.
What I need is to be able to select both Sunday and Wednesday and get a total of 4 + 5 = 9.
I've used this formula:
total_count = var Sunday = IF(VALUES(day_of_week[week_day]) = "Sunday" && HASONEVALUE(day_of_week[week_day]), VALUES(day_of_week[staff_day_of_week]),0)
var Monday = IF(VALUES(day_of_week[week_day]) = "Monday" && HASONEVALUE(day_of_week[week_day]), VALUES(day_of_week[staff_day_of_week]),0)
var Tuesday = IF(VALUES(day_of_week[week_day]) = "Tuesday" && HASONEVALUE(day_of_week[week_day]), VALUES(day_of_week[staff_day_of_week]),0)
var Wednesday = IF(VALUES(day_of_week[week_day]) = "Wednesday" && HASONEVALUE(day_of_week[week_day]), VALUES(day_of_week[staff_day_of_week]),0)
var sunday_total = if(Sunday = 1, INT((WEEKDAY([start_of_month] - Sunday) - [start_of_month] + [end_of_month]) / 7),0)
var monday_total = if(Monday = 2, INT((WEEKDAY([start_of_month] - Monday) - [start_of_month] + [end_of_month]) / 7),0)
var tuesday_total = if(Tuesday = 3, INT((WEEKDAY([start_of_month] - Tuesday) - [start_of_month] + [end_of_month]) / 7),0)
var wednesday_total = if(Wednesday = 4, INT((WEEKDAY([start_of_month] - Wednesday) - [start_of_month] + [end_of_month]) / 7),0)
var sum_total = sunday_total + monday_total + tuesday_total + wednesday_total
return sum_total
And so on.
[start_of_mont] and [end_of_month] meauses from my table an example is:
end_of_month = ENDOFMONTH(staff_units[staff_begin_local_date])
thinking I could build on it however, when I don't select anything I get an error when I select 1 I get 4 the expected results however, if I select two Sunday and Wednesday I get an errorr.
The summary of the error says MdxScript(Model) (34, 52) Calculation error in measure 'staff_units'[sunday_count]: A table of multiple values was supplied where a single value was expected.
I can't seem to get the formula to take multiple days or none I hope someone can help me on this thank you?
Antonio
Solved! Go to Solution.
Hi Antonio,
What is structure of table "staff_units"? Are the dates continuous in this table? I created a sample like this.
Calendar = ADDCOLUMNS ( CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2017, 12, 31 ) ), "WeekDay", WEEKDAY ( [Date] ), "Year", YEAR ( [Date] ), "Month", FORMAT ( [Date], "MMMM" ) )
TotalWeekDay = COUNT ( 'Calendar'[WeekDay] )
Best Regards!
Dale
Here is the formula:
sum_of_days_in_month = SUMX(
SUMMARIZE(
staff_units,
staff_units[staff_day_of_week],
staff_units[days_of_weeks_in_month]
),
staff_units[days_of_weeks_in_month]
)
I added a column to the staff_unit table called days_of_weeks_in_month this column only looks at the begining and end dates of the month and calculates how many of the Monday, Tuesday, etc. days in the month then it takes the number day of week as well summarize's (groups it) then sums the distinct days of the week in month.
Wow, what a way to go it took days to get here.
Thank you so much for keeping up with me I'm still not done with this calculation I have rooms and locations along with minutes before I can even apply the statistics.
I have a filter from a table called day_of_week the table contains 2 columns:
(Whole Number) (string)
staff_day_of_week week_day
1 Sunday
2 Monday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday
I'm setting up a filter that allows the user to select any different combinations of day of the week.
I need to calculate for any giving month or year the total of the sum of all days that are select.
For example in March of 2016 the total Sunday's in the month are 4 however, the total Tuesday's equal 5.
What I need is to be able to select both Sunday and Wednesday and get a total of 4 + 5 = 9.
I've used this formula:
total_count = var Sunday = IF(VALUES(day_of_week[week_day]) = "Sunday" && HASONEVALUE(day_of_week[week_day]), VALUES(day_of_week[staff_day_of_week]),0)
var Monday = IF(VALUES(day_of_week[week_day]) = "Monday" && HASONEVALUE(day_of_week[week_day]), VALUES(day_of_week[staff_day_of_week]),0)
var Tuesday = IF(VALUES(day_of_week[week_day]) = "Tuesday" && HASONEVALUE(day_of_week[week_day]), VALUES(day_of_week[staff_day_of_week]),0)
var Wednesday = IF(VALUES(day_of_week[week_day]) = "Wednesday" && HASONEVALUE(day_of_week[week_day]), VALUES(day_of_week[staff_day_of_week]),0)
var sunday_total = if(Sunday = 1, INT((WEEKDAY([start_of_month] - Sunday) - [start_of_month] + [end_of_month]) / 7),0)
var monday_total = if(Monday = 2, INT((WEEKDAY([start_of_month] - Monday) - [start_of_month] + [end_of_month]) / 7),0)
var tuesday_total = if(Tuesday = 3, INT((WEEKDAY([start_of_month] - Tuesday) - [start_of_month] + [end_of_month]) / 7),0)
var wednesday_total = if(Wednesday = 4, INT((WEEKDAY([start_of_month] - Wednesday) - [start_of_month] + [end_of_month]) / 7),0)
var sum_total = sunday_total + monday_total + tuesday_total + wednesday_total
return sum_total
And so on.
[start_of_mont] and [end_of_month] meauses from my table an example is:
end_of_month = ENDOFMONTH(staff_units[staff_begin_local_date])
thinking I could build on it however, when I don't select anything I get an error when I select 1 I get 4 the expected results however, if I select two Sunday and Wednesday I get an errorr.
The summary of the error says MdxScript(Model) (34, 52) Calculation error in measure 'staff_units'[sunday_count]: A table of multiple values was supplied where a single value was expected.
I can't seem to get the formula to take multiple days or none I hope someone can help me on this thank you?
Antonio
Hi Antonio,
What is structure of table "staff_units"? Are the dates continuous in this table? I created a sample like this.
Calendar = ADDCOLUMNS ( CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2017, 12, 31 ) ), "WeekDay", WEEKDAY ( [Date] ), "Year", YEAR ( [Date] ), "Month", FORMAT ( [Date], "MMMM" ) )
TotalWeekDay = COUNT ( 'Calendar'[WeekDay] )
Best Regards!
Dale
Here is a sample of the data in staff_units:
This is the format of the table it is related to day_of_week by the staff_day_of_week in both tables.
This is as far as I've gotten on the formula as well, it doesn't work.
Let me know or if you something else. Thank you!
total_units_v =
var sunday = IF(SELECTEDVALUE(day_of_week[week_day], "Sunday") = "Sunday", INT((WEEKDAY([start_of_month] - 1) - [start_of_month] + [end_of_month]) / 7), 0)
var monday = If(SELECTEDVALUE(day_of_week[week_day], "Monday") = "Monday", INT((WEEKDAY([start_of_month] - 2) - [start_of_month] + [end_of_month]) / 7), 0)
var tuesday = If(SELECTEDVALUE(day_of_week[week_day], "Tuesday") = "Tuesday", INT((WEEKDAY([start_of_month] - 3) - [start_of_month] + [end_of_month]) / 7), 0)
var wednesday = If(SELECTEDVALUE(day_of_week[week_day], "Wednesday") = "Wednesday", INT((WEEKDAY([start_of_month] - 4) - [start_of_month] + [end_of_month]) / 7), 0)
var thursday = IF(SELECTEDVALUE(day_of_week[week_day], "Thursday") = "Thursday", INT((WEEKDAY([start_of_month] - 5) - [start_of_month] + [end_of_month]) / 7), 0)
var friday = IF(SELECTEDVALUE(day_of_week[week_day], "Friday") = "Friday", INT((WEEKDAY([start_of_month] - 6) - [start_of_month] + [end_of_month]) / 7), 0)
var saturday = If(SELECTEDVALUE(day_of_week[week_day], "Saturday") = "Saturday", INT((WEEKDAY([start_of_month] - 7) - [start_of_month] + [end_of_month]) / 7), 0)
var selected_units = if(DISTINCTCOUNT(day_of_week[week_day]) = CALCULATE(DISTINCTCOUNT(day_of_week[week_day]), ALL(day_of_week)),
if(HASONEVALUE(month_year[month_of_year]), int([end_of_month] - [start_of_month]) + 1, int([end_of_year] - [start_of_year]) + 1),
If(DISTINCTCOUNT(day_of_week[week_day])=1,
If(HASONEVALUE(day_of_week[staff_day_of_week]), SWITCH(VALUES(day_of_week[staff_day_of_week]),
1, INT((WEEKDAY([start_of_month] - 1) - [start_of_month] + [end_of_month]) / 7),
2, INT((WEEKDAY([start_of_month] - 2) - [start_of_month] + [end_of_month]) / 7),
3, INT((WEEKDAY([start_of_month] - 3) - [start_of_month] + [end_of_month]) / 7),
4, INT((WEEKDAY([start_of_month] - 4) - [start_of_month] + [end_of_month]) / 7),
5, INT((WEEKDAY([start_of_month] - 5) - [start_of_month] + [end_of_month]) / 7),
6, INT((WEEKDAY([start_of_month] - 6) - [start_of_month] + [end_of_month]) / 7),
7, INT((WEEKDAY([start_of_month] - 7) - [start_of_month] + [end_of_month]) / 7)), 0),
0))
return selected_units
location_name | room | staff_an_52_enc_csn_id | staff_begin_local_date | staff_begin_local_dttm | staff_begin_local_year | staff_day_of_week | staff_end_local_dttm | staff_episode_id | staff_hsp_pat_enc_csn_id | staff_patience_id | staff_provider_name | staff_provider_type | staff_provider_type_abbr | staff_total_minutes | staff_units_per_hour | staff_year_month | time_24_hour_short_1 |
Location | Room 1 | 1000000000 | 2/8/2016 | 2/8/2016 | 2016 | 2 | 2/8/2016 | 1000000 | 1000000000 | 1111111 | Provider Name | Provider Type | Provider Type Abbr | 78 | 8 | 201602 | 15 |
Location | Room 1 | 2000000000 | 2/8/2016 | 2/8/2016 | 2016 | 2 | 2/8/2016 | 1000000 | 2000000000 | 1111111 | Provider Name | Provider Type | Provider Type Abbr | 78 | 60 | 201602 | 16 |
Location | Room 1 | 3000000000 | 2/8/2016 | 2/8/2016 | 2016 | 2 | 2/8/2016 | 1000000 | 3000000000 | 1111111 | Provider Name | Provider Type | Provider Type Abbr | 78 | 10 | 201602 | 17 |
Location | Room 2 | 4000000000 | 2/17/2016 | 2/17/2016 | 2016 | 4 | 2/17/2016 | 2000000 | 4000000000 | 2222222 | Provider Name | Provider Type | Provider Type Abbr | 113 | 9 | 201602 | 13 |
Location | Room 2 | 5000000000 | 2/17/2016 | 2/17/2016 | 2016 | 4 | 2/17/2016 | 2000000 | 5000000000 | 2222222 | Provider Name | Provider Type | Provider Type Abbr | 113 | 60 | 201602 | 14 |
Location | Room 2 | 6000000000 | 2/17/2016 | 2/17/2016 | 2016 | 4 | 2/17/2016 | 2000000 | 6000000000 | 2222222 | Provider Name | Provider Type | Provider Type Abbr | 113 | 44 | 201602 | 15 |
Location | Room 3 | 7000000000 | 2/17/2016 | 2/17/2016 | 2016 | 4 | 2/17/2016 | 3000000 | 7000000000 | 3333333 | Provider Name | Provider Type | Provider Type Abbr | 76 | 56 | 201602 | 16 |
Location | Room 3 | 8000000000 | 2/17/2016 | 2/17/2016 | 2016 | 4 | 2/17/2016 | 3000000 | 8000000000 | 3333333 | Provider Name | Provider Type | Provider Type Abbr | 76 | 20 | 201602 | 17 |
No staff_units dates are not continuous, they miss days or even months as they are the detail of the data and I cannot keep that much information as this goes to the minute. What staff_units contains in a breakdown of minutes in each hour so the volumn is way to large as this if based on a group of hospitals and each room is being tracked.
In the end I have a formula that calculates the number of sundays, mondays, etc. for any given period so that I can multiple that number by 60 to get the denominator for the utilization calculation.
So far this is what I have built the problem is the last 0 I need to do the same calculation as the switch but only for the selected values.
total_units_v = var selected_units = if(DISTINCTCOUNT(day_of_week[week_day]) = CALCULATE(DISTINCTCOUNT(day_of_week[week_day]), ALL(day_of_week)),
if(HASONEVALUE(month_year[month_of_year]), int([end_of_month] - [start_of_month]) + 1, int([end_of_year] - [start_of_year]) + 1),
If(DISTINCTCOUNT(day_of_week[week_day])=1,
If(HASONEVALUE(day_of_week[staff_day_of_week]), SWITCH(VALUES(day_of_week[staff_day_of_week]),
1, INT((WEEKDAY([start_of_month] - 1) - [start_of_month] + [end_of_month]) / 7),
2, INT((WEEKDAY([start_of_month] - 2) - [start_of_month] + [end_of_month]) / 7),
3, INT((WEEKDAY([start_of_month] - 3) - [start_of_month] + [end_of_month]) / 7),
4, INT((WEEKDAY([start_of_month] - 4) - [start_of_month] + [end_of_month]) / 7),
5, INT((WEEKDAY([start_of_month] - 5) - [start_of_month] + [end_of_month]) / 7),
6, INT((WEEKDAY([start_of_month] - 6) - [start_of_month] + [end_of_month]) / 7),
7, INT((WEEKDAY([start_of_month] - 7) - [start_of_month] + [end_of_month]) / 7)), 0),
0))
return selected_units
I can't seem to figure it out can anyone help?
Thank you,
Antonio
Hi @antoniofallucca,
My example works in non-continuous data. Could you please share a sample?
Best Regards!
Dale
I thought your example worked however, it doesn't it only counts the days that there are values I used my date dimension table as slected monday and tuesday for feb 2016 and the count I got was only what was in my fact table so this is not the solution I'm looking for. I actually look to count all of the days in Feb 2016 for what I have selected the count should have been 9 not 4. Unless I've missed something can you please take a look at the sample I data I sent and see if you see anything I could have missed.
Thank you.
Hi Antonio,
1. Did you mean you want to get the actual total of selected weekdays? For instance, there are 4 Friday and 4 Saturday in 201708, if user select Friday and Saturday and 201708, the result should be 8, even there are no records in "staff_units".
2. You said you want to select year and month. Where are they from? Which table? Which column?
If these two questions are clear, the solution could be easy.
Best Regard!
Dale
Here is the formula:
sum_of_days_in_month = SUMX(
SUMMARIZE(
staff_units,
staff_units[staff_day_of_week],
staff_units[days_of_weeks_in_month]
),
staff_units[days_of_weeks_in_month]
)
I added a column to the staff_unit table called days_of_weeks_in_month this column only looks at the begining and end dates of the month and calculates how many of the Monday, Tuesday, etc. days in the month then it takes the number day of week as well summarize's (groups it) then sums the distinct days of the week in month.
Wow, what a way to go it took days to get here.
Thank you so much for keeping up with me I'm still not done with this calculation I have rooms and locations along with minutes before I can even apply the statistics.
Hi Antonio,
I have read your post carefully today. It seems you had worked it out. That's great! I attached the file just for your reference: https://1drv.ms/u/s!ArTqPk2pu-BkgQ7tL16raYFK75QZ
Best Regards!
Dale
Hi Dales,
1. Did you mean you want to get the actual total of selected weekdays? For instance, there are 4 Friday and 4 Saturday in 201708, if user select Friday and Saturday and 201708, the result should be 8, even there are no records in "staff_units".
A. yes
2. You said you want to select year and month. Where are they from? Which table? Which column?
If these two questions are clear, the solution could be easy.
A. the fact table is staff_units, I have a date dimension table it is fully populated with both days and every date begining with 1900.
Here is my problem and I'll post more sample data so you can see it.
staff_day_of_week | week_day |
1 | Sunday |
2 | Monday |
3 | Tuesday |
4 | Wednesday |
5 | Thursday |
6 | Friday |
7 | Saturday |
My date dimension table has a relationship with the staff_day_of_week column, as well as a relationship with the staff_unit fact table on the staff_day_of_week (either can be used). At the moment I’m using a relationship with the date dimension table.
What I need to do is count the days in any giving month so if I selected Friday and Saturday and 201708 I should get 8 even if there is no data in the fact table for Friday and Saturday.
Here is the sample data for the staff_units table:
location_name room staff_begin_local_date staff_begin_local_dttm staff_begin_local_year staff_day_of_week staff_end_local_dttm staff_episode_id staff_patience_id staff_provider_name staff_provider_type staff_provider_type_abbr staff_total_minutes staff_units_per_hour staff_year_month
location_name | room | staff_begin_local_date | staff_begin_local_year | staff_day_of_week | staff_end_local_dttm | staff_episode_id | staff_patience_id | staff_provider_name | staff_provider_type | staff_total_minutes | staff_units_per_hour | staff_year_month |
Location 1 | Room 3 | 3/16/2016 | 2016 | 4 | 3/16/2016 | 1000000 | 1100000 | Provider Name 1 | Type | 336 | 112 | 201603 |
Location 1 | Room 3 | 3/16/2016 | 2016 | 4 | 3/16/2016 | 1100000 | 1210000 | Provider Name 1 | Type | 375 | 125 | 201603 |
Location 1 | Room 3 | 3/16/2016 | 2016 | 4 | 3/16/2016 | 1110000 | 1221000 | Provider Name 1 | Type | 188 | 94 | 201603 |
Location 1 | Room 3 | 3/24/2016 | 2016 | 5 | 3/24/2016 | 1111000 | 1222100 | Provider Name 1 | Type | 372 | 124 | 201603 |
Location 1 | Room 3 | 3/24/2016 | 2016 | 5 | 3/24/2016 | 1111100 | 1222210 | Provider Name 1 | Type | 420 | 140 | 201603 |
Location 1 | Room 3 | 3/24/2016 | 2016 | 5 | 3/24/2016 | 1156380 | 1272018 | Provider Name 1 | Type | 8 | 8 | 201603 |
Location 1 | Room 3 | 3/29/2016 | 2016 | 3 | 3/29/2016 | 1179700 | 1297670 | Provider Name 1 | Type | 369 | 123 | 201603 |
Location 1 | Room 3 | 3/29/2016 | 2016 | 3 | 3/29/2016 | 1203020 | 1323322 | Provider Name 1 | Type | 1175 | 235 | 201603 |
Location 1 | Room 4 | 3/2/2016 | 2016 | 4 | 3/2/2016 | 1226340 | 1348974 | Provider Name 1 | Type | 387 | 129 | 201603 |
Location 1 | Room 4 | 3/22/2016 | 2016 | 3 | 3/22/2016 | 1249660 | 1374626 | Provider Name 1 | Type | 166 | 83 | 201603 |
Location 1 | Room 4 | 3/22/2016 | 2016 | 3 | 3/22/2016 | 1272980 | 1400278 | Provider Name 1 | Type | 333 | 111 | 201603 |
Location 1 | Room 4 | 3/22/2016 | 2016 | 3 | 3/22/2016 | 1296300 | 1425930 | Provider Name 1 | Type | 182 | 91 | 201603 |
Location 1 | Room 4 | 3/23/2016 | 2016 | 4 | 3/23/2016 | 1319620 | 1451582 | Provider Name 1 | Type | 130 | 65 | 201603 |
Location 1 | Room 4 | 3/23/2016 | 2016 | 4 | 3/23/2016 | 1342940 | 1477234 | Provider Name 1 | Type | 249 | 83 | 201603 |
Location 1 | Room 4 | 3/23/2016 | 2016 | 4 | 3/23/2016 | 1366260 | 1502886 | Provider Name 1 | Type | 411 | 137 | 201603 |
Location 1 | Room 4 | 3/23/2016 | 2016 | 4 | 3/23/2016 | 1389580 | 1528538 | Provider Name 1 | Type | 237 | 79 | 201603 |
Location 1 | Room 4 | 3/25/2016 | 2016 | 6 | 3/25/2016 | 1412900 | 1554190 | Provider Name 1 | Type | 130 | 65 | 201603 |
Location 1 | Room 4 | 3/25/2016 | 2016 | 6 | 3/25/2016 | 1436220 | 1579842 | Provider Name 1 | Type | 128 | 64 | 201603 |
Location 1 | Room 4 | 3/25/2016 | 2016 | 6 | 3/25/2016 | 1459540 | 1605494 | Provider Name 1 | Type | 56 | 56 | 201603 |
Location 1 | Room 4 | 3/25/2016 | 2016 | 6 | 3/25/2016 | 9999999 | 9999991 | Provider Name 1 | Type | 16 | 16 | 201603 |
Location 1 | Room 4 | 3/25/2016 | 2016 | 6 | 3/25/2016 | 9999999 | 9999991 | Provider Name 1 | Type | 48 | 24 | 201603 |
Location 1 | Room 4 | 3/25/2016 | 2016 | 6 | 3/25/2016 | 2000000 | 2200000 | Provider Name 1 | Type | 172 | 86 | 201603 |
Location 1 | Room 6 | 3/15/2016 | 2016 | 3 | 3/15/2016 | 2200000 | 2420000 | Provider Name 1 | Type | 90 | 45 | 201603 |
Location 1 | Room C01 | 3/28/2016 | 2016 | 2 | 3/28/2016 | 2220000 | 2442000 | Provider Name 1 | Type | 70 | 35 | 201603 |
Location 1 | Room C01 | 3/28/2016 | 2016 | 2 | 3/28/2016 | 2222000 | 2444200 | Provider Name 1 | Type | 122 | 61 | 201603 |
Location 1 | Room C01 | 3/28/2016 | 2016 | 2 | 3/28/2016 | 2332000 | 2565200 | Provider Name 1 | Type | 56 | 28 | 201603 |
Location 1 | Room C01 | 3/28/2016 | 2016 | 2 | 3/28/2016 | 2400600 | 2640660 | Provider Name 1 | Type | 94 | 47 | 201603 |
Location 1 | Room C01 | 3/28/2016 | 2016 | 2 | 3/28/2016 | 2469200 | 2716120 | Provider Name 1 | Type | 86 | 43 | 201603 |
Location 1 | Room C01 | 3/28/2016 | 2016 | 2 | 3/28/2016 | 2537800 | 2791580 | Provider Name 1 | Type | 112 | 56 | 201603 |
Location 1 | Room C01 | 3/28/2016 | 2016 | 2 | 3/28/2016 | 2606400 | 2867040 | Provider Name 1 | Type | 258 | 86 | 201603 |
Location 2 | Room RC01 | 3/7/2016 | 2016 | 2 | 3/7/2016 | 2675000 | 2942500 | Provider Name 2 | Type | 29 | 29 | 201603 |
Location 2 | Room RC01 | 3/7/2016 | 2016 | 2 | 3/7/2016 | 2743600 | 3017960 | Provider Name 2 | Type | 23 | 23 | 201603 |
Location 2 | Room RC01 | 3/8/2016 | 2016 | 3 | 3/8/2016 | 2812200 | 3093420 | Provider Name 2 | Type | 62 | 31 | 201603 |
Location 2 | Room RC01 | 3/8/2016 | 2016 | 3 | 3/8/2016 | 2880800 | 3168880 | Provider Name 2 | Type | 36 | 18 | 201603 |
Location 2 | Room RC01 | 3/10/2016 | 2016 | 5 | 3/10/2016 | 2949400 | 3244340 | Provider Name 2 | Type | 33 | 66 | 201603 |
Location 2 | Room RC01 | 3/10/2016 | 2016 | 5 | 3/10/2016 | 3018000 | 3319800 | Provider Name 2 | Type | 98 | 147 | 201603 |
Location 2 | Room RC01 | 3/10/2016 | 2016 | 5 | 3/10/2016 | 3086600 | 3395260 | Provider Name 2 | Type | 80 | 160 | 201603 |
Location 2 | Room RC01 | 3/14/2016 | 2016 | 2 | 3/14/2016 | 3155200 | 3470720 | Provider Name 3 | Type | 21 | 21 | 201603 |
Location 2 | Room RC01 | 3/15/2016 | 2016 | 3 | 3/15/2016 | 3223800 | 3546180 | Provider Name 4 | Type | 44 | 44 | 201603 |
Location 2 | Room RC01 | 3/15/2016 | 2016 | 3 | 3/15/2016 | 3292400 | 3621640 | Provider Name 4 | Type | 90 | 45 | 201603 |
Location 2 | Room RC01 | 3/21/2016 | 2016 | 2 | 3/21/2016 | 3361000 | 3697100 | Provider Name 4 | Type | 116 | 58 | 201603 |
Location 2 | Room RC01 | 3/22/2016 | 2016 | 3 | 3/22/2016 | 3429600 | 3772560 | Provider Name 5 | Type | 25 | 25 | 201603 |
Location 2 | Room RC01 | 3/24/2016 | 2016 | 5 | 3/24/2016 | 3498200 | 3848020 | Provider Name 6 | Type | 21 | 21 | 201603 |
Location 2 | Room RC01 | 3/28/2016 | 2016 | 2 | 3/28/2016 | 3566800 | 3923480 | Provider Name 6 | Type | 16 | 16 | 201603 |
Location 2 | Room RC01 | 3/29/2016 | 2016 | 3 | 3/29/2016 | 3635400 | 3998940 | Provider Name 7 | Type | 26 | 26 | 201603 |
Location 2 | Room RC01 | 3/29/2016 | 2016 | 3 | 3/29/2016 | 3704000 | 4074400 | Provider Name 7 | Type | 20 | 20 | 201603 |
Location 2 | Room RC01 | 3/29/2016 | 2016 | 3 | 3/29/2016 | 3772600 | 4149860 | Provider Name 7 | Type | 31 | 31 | 201603 |
Location 3 | Room D01 | 3/6/2016 | 2016 | 1 | 3/6/2016 | 3841200 | 4225320 | Provider Name 7 | Type | 776 | 194 | 201603 |
Location 3 | Room D01 | 3/8/2016 | 2016 | 3 | 3/8/2016 | 3909800 | 4300780 | Provider Name 7 | Type | 288 | 96 | 201603 |
Location 3 | Room D01 | 3/13/2016 | 2016 | 1 | 3/13/2016 | 3978400 | 4376240 | Provider Name 8 | Type | 740 | 185 | 201603 |
Location 4 | Room S01 | 3/10/2016 | 2016 | 5 | 3/10/2016 | 4047000 | 4451700 | Provider Name 8 | Type | 279 | 186 | 201603 |
Location 4 | Room S01 | 3/23/2016 | 2016 | 4 | 3/23/2016 | 4115600 | 4527160 | Provider Name 8 | Type | 194 | 97 | 201603 |
Location 4 | Room S01 | 3/25/2016 | 2016 | 6 | 3/25/2016 | 4184200 | 4602620 | Provider Name 8 | Type | 201 | 67 | 201603 |
Location 4 | Room S01 | 3/29/2016 | 2016 | 3 | 3/29/2016 | 4252800 | 4678080 | Provider Name 9 | Type | 258 | 86 | 201603 |
Location 4 | Room S01 | 3/10/2016 | 2016 | 5 | 3/10/2016 | 4321400 | 4753540 | Provider Name 10 | Type | 92 | 92 | 201603 |
Location 4 | Room S01 | 3/10/2016 | 2016 | 5 | 3/10/2016 | 4390000 | 4829000 | Provider Name 10 | Type | 120 | 180 | 201603 |
Let me know if I you can think of anything?
Thank you for this help, I'm trying to show the company I work for that Power BI is a more effective solution and of course they gave me a challenge that's never been done.
Can you share some data of the StaffUnit Table on one drive and provide the link.
Cheers
CheenuSing
I under a couple of dead lines I haven't had a chance to get you any data I will either tomorrow or the next day. The staff_unit table is nothing more than a fact table I created from a report file. The system we are receiving data from can't do much and it is a third party. The data contained in the staff_unit is by episode and an episode can span over multiple hours so at 9:00 AM they could have 10 minutes, then at 10:00 AM it could be 60 minutes and then at 11:00 AM it finishes with 15 minutes. There are rooms involved so each room as well becuase each episode takes place in a room. The data is not continous in the staff_unit by day so I can be missing days that is the reason for doing a calculation to find the number of days like monday and tuesday combined then I can do the mulitiplation to determine how many possible minutes are available. From there I can do the stats on the information.
Like I said I'll try to get you some data either tomorrow in the afternoon or the next day unless you can help me fully understand your example. Is Calendar a fact table and it just is substituting for staff_units?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
84 | |
73 | |
49 |
User | Count |
---|---|
143 | |
131 | |
110 | |
64 | |
55 |