cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Dax Measure to select multiple filters from a table

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

2 ACCEPTED SOLUTIONS
Microsoft Employee

Hi Antonio,

What is structure of table "staff_units"? Are the dates continuous in this table? I created a sample like this.

```Calendar =
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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

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.

13 REPLIES 13
Helper I

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

Microsoft Employee

Hi Antonio,

What is structure of table "staff_units"? Are the dates continuous in this table? I created a sample like this.

```Calendar =
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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

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
Helper I

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.

Helper I

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

Microsoft Employee

My example works in non-continuous data. Could you please share a sample?

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

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.

Microsoft Employee

@antoniofallucca

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

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.

Microsoft Employee

@antoniofallucca

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

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.

1. I need to calculate a numerator for all the days by hour (number of minutes used) by room in my fact table.
2. I have already calculated this.
3. I need to calculate the denominator as a total of days in a period regardless of whether there is a day for it in the fact table.
4. I have already calculated the total meaning no filter,
5. Plus the year,
6. The month and,
7. One day.
8. I cannot the calculation for more than one day to work.
9. I have a standard date dimension table by day.
10. I also have day of week table used as a filter for the individual (I Know this is not necessary however, I have made a choice to use this), I’m including the table here:
 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.

Community Champion

Can you share some data of the StaffUnit Table on one drive and provide the link.

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Helper I

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors