Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead 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

Reply
antoniofallucca
Helper I
Helper I

Dax Measure to select multiple filters from a table

‎08-18-2017 02:13 PM

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

 

Message 1 of 14
4,914 Views
0
2 ACCEPTED SOLUTIONS
v-jiascu-msft
Microsoft Employee
Microsoft Employee
In response to antoniofallucca

‎08-21-2017 12:45 AM

@antoniofallucca,

 

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] )

Dax formula to select multiple paramters from a filter .jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

View solution in original post

Message 3 of 14
5,721 Views
0
antoniofallucca
Helper I
Helper I
In response to v-jiascu-msft

‎08-24-2017 11:31 AM

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.

View solution in original post

Message 12 of 14
5,023 Views
13 REPLIES 13
antoniofallucca
Helper I
Helper I

‎08-18-2017 02:18 PM

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

 

Message 2 of 14
4,885 Views
0
v-jiascu-msft
Microsoft Employee
Microsoft Employee
In response to antoniofallucca

‎08-21-2017 12:45 AM

@antoniofallucca,

 

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] )

Dax formula to select multiple paramters from a filter .jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.
Message 3 of 14
5,722 Views
0
antoniofallucca
Helper I
Helper I
In response to v-jiascu-msft

‎08-23-2017 08:53 AM

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_nameroomstaff_an_52_enc_csn_idstaff_begin_local_datestaff_begin_local_dttmstaff_begin_local_yearstaff_day_of_weekstaff_end_local_dttmstaff_episode_idstaff_hsp_pat_enc_csn_idstaff_patience_idstaff_provider_namestaff_provider_typestaff_provider_type_abbrstaff_total_minutesstaff_units_per_hourstaff_year_monthtime_24_hour_short_1
LocationRoom 110000000002/8/20162/8/2016201622/8/2016100000010000000001111111Provider NameProvider TypeProvider Type Abbr78820160215
LocationRoom 120000000002/8/20162/8/2016201622/8/2016100000020000000001111111Provider NameProvider TypeProvider Type Abbr786020160216
LocationRoom 130000000002/8/20162/8/2016201622/8/2016100000030000000001111111Provider NameProvider TypeProvider Type Abbr781020160217
LocationRoom 240000000002/17/20162/17/2016201642/17/2016200000040000000002222222Provider NameProvider TypeProvider Type Abbr113920160213
LocationRoom 250000000002/17/20162/17/2016201642/17/2016200000050000000002222222Provider NameProvider TypeProvider Type Abbr1136020160214
LocationRoom 260000000002/17/20162/17/2016201642/17/2016200000060000000002222222Provider NameProvider TypeProvider Type Abbr1134420160215
LocationRoom 370000000002/17/20162/17/2016201642/17/2016300000070000000003333333Provider NameProvider TypeProvider Type Abbr765620160216
LocationRoom 380000000002/17/20162/17/2016201642/17/2016300000080000000003333333Provider NameProvider TypeProvider Type Abbr762020160217
Message 14 of 14
4,792 Views
0
antoniofallucca
Helper I
Helper I
In response to v-jiascu-msft

‎08-21-2017 07:21 AM

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.

Message 4 of 14
4,850 Views
0
antoniofallucca
Helper I
Helper I
In response to antoniofallucca

‎08-21-2017 10:43 AM

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

Message 5 of 14
4,840 Views
0
v-jiascu-msft
Microsoft Employee
Microsoft Employee
In response to antoniofallucca

‎08-21-2017 10:54 PM

Hi @antoniofallucca,

 

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.
Message 8 of 14
4,828 Views
0
antoniofallucca
Helper I
Helper I
In response to v-jiascu-msft

‎08-23-2017 09:39 AM

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.

Message 9 of 14
4,785 Views
0
v-jiascu-msft
Microsoft Employee
Microsoft Employee
In response to antoniofallucca

‎08-24-2017 02:31 AM

@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.
Message 10 of 14
3,181 Views
0
antoniofallucca
Helper I
Helper I
In response to v-jiascu-msft

‎08-24-2017 11:31 AM

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.

Message 12 of 14
5,024 Views
v-jiascu-msft
Microsoft Employee
Microsoft Employee
In response to antoniofallucca

‎08-28-2017 06:33 PM

@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.
Message 13 of 14
3,136 Views
0
antoniofallucca
Helper I
Helper I
In response to v-jiascu-msft

‎08-24-2017 07:43 AM

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_nameroomstaff_begin_local_datestaff_begin_local_yearstaff_day_of_weekstaff_end_local_dttmstaff_episode_idstaff_patience_idstaff_provider_namestaff_provider_typestaff_total_minutesstaff_units_per_hourstaff_year_month
Location 1Room 33/16/2016201643/16/201610000001100000Provider Name 1Type336112201603
Location 1Room 33/16/2016201643/16/201611000001210000Provider Name 1Type375125201603
Location 1Room 33/16/2016201643/16/201611100001221000Provider Name 1Type18894201603
Location 1Room 33/24/2016201653/24/201611110001222100Provider Name 1Type372124201603
Location 1Room 33/24/2016201653/24/201611111001222210Provider Name 1Type420140201603
Location 1Room 33/24/2016201653/24/201611563801272018Provider Name 1Type88201603
Location 1Room 33/29/2016201633/29/201611797001297670Provider Name 1Type369123201603
Location 1Room 33/29/2016201633/29/201612030201323322Provider Name 1Type1175235201603
Location 1Room 43/2/2016201643/2/201612263401348974Provider Name 1Type387129201603
Location 1Room 43/22/2016201633/22/201612496601374626Provider Name 1Type16683201603
Location 1Room 43/22/2016201633/22/201612729801400278Provider Name 1Type333111201603
Location 1Room 43/22/2016201633/22/201612963001425930Provider Name 1Type18291201603
Location 1Room 43/23/2016201643/23/201613196201451582Provider Name 1Type13065201603
Location 1Room 43/23/2016201643/23/201613429401477234Provider Name 1Type24983201603
Location 1Room 43/23/2016201643/23/201613662601502886Provider Name 1Type411137201603
Location 1Room 43/23/2016201643/23/201613895801528538Provider Name 1Type23779201603
Location 1Room 43/25/2016201663/25/201614129001554190Provider Name 1Type13065201603
Location 1Room 43/25/2016201663/25/201614362201579842Provider Name 1Type12864201603
Location 1Room 43/25/2016201663/25/201614595401605494Provider Name 1Type5656201603
Location 1Room 43/25/2016201663/25/201699999999999991Provider Name 1Type1616201603
Location 1Room 43/25/2016201663/25/201699999999999991Provider Name 1Type4824201603
Location 1Room 43/25/2016201663/25/201620000002200000Provider Name 1Type17286201603
Location 1Room 63/15/2016201633/15/201622000002420000Provider Name 1Type9045201603
Location 1Room C013/28/2016201623/28/201622200002442000Provider Name 1Type7035201603
Location 1Room C013/28/2016201623/28/201622220002444200Provider Name 1Type12261201603
Location 1Room C013/28/2016201623/28/201623320002565200Provider Name 1Type5628201603
Location 1Room C013/28/2016201623/28/201624006002640660Provider Name 1Type9447201603
Location 1Room C013/28/2016201623/28/201624692002716120Provider Name 1Type8643201603
Location 1Room C013/28/2016201623/28/201625378002791580Provider Name 1Type11256201603
Location 1Room C013/28/2016201623/28/201626064002867040Provider Name 1Type25886201603
Location 2Room RC013/7/2016201623/7/201626750002942500Provider Name 2Type2929201603
Location 2Room RC013/7/2016201623/7/201627436003017960Provider Name 2Type2323201603
Location 2Room RC013/8/2016201633/8/201628122003093420Provider Name 2Type6231201603
Location 2Room RC013/8/2016201633/8/201628808003168880Provider Name 2Type3618201603
Location 2Room RC013/10/2016201653/10/201629494003244340Provider Name 2Type3366201603
Location 2Room RC013/10/2016201653/10/201630180003319800Provider Name 2Type98147201603
Location 2Room RC013/10/2016201653/10/201630866003395260Provider Name 2Type80160201603
Location 2Room RC013/14/2016201623/14/201631552003470720Provider Name 3Type2121201603
Location 2Room RC013/15/2016201633/15/201632238003546180Provider Name 4Type4444201603
Location 2Room RC013/15/2016201633/15/201632924003621640Provider Name 4Type9045201603
Location 2Room RC013/21/2016201623/21/201633610003697100Provider Name 4Type11658201603
Location 2Room RC013/22/2016201633/22/201634296003772560Provider Name 5Type2525201603
Location 2Room RC013/24/2016201653/24/201634982003848020Provider Name 6Type2121201603
Location 2Room RC013/28/2016201623/28/201635668003923480Provider Name 6Type1616201603
Location 2Room RC013/29/2016201633/29/201636354003998940Provider Name 7Type2626201603
Location 2Room RC013/29/2016201633/29/201637040004074400Provider Name 7Type2020201603
Location 2Room RC013/29/2016201633/29/201637726004149860Provider Name 7Type3131201603
Location 3Room D013/6/2016201613/6/201638412004225320Provider Name 7Type776194201603
Location 3Room D013/8/2016201633/8/201639098004300780Provider Name 7Type28896201603
Location 3Room D013/13/2016201613/13/201639784004376240Provider Name 8Type740185201603
Location 4Room S013/10/2016201653/10/201640470004451700Provider Name 8Type279186201603
Location 4Room S013/23/2016201643/23/201641156004527160Provider Name 8Type19497201603
Location 4Room S013/25/2016201663/25/201641842004602620Provider Name 8Type20167201603
Location 4Room S013/29/2016201633/29/201642528004678080Provider Name 9Type25886201603
Location 4Room S013/10/2016201653/10/201643214004753540Provider Name 10Type9292201603
Location 4Room S013/10/2016201653/10/201643900004829000Provider Name 10Type120180201603

 

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.

Message 11 of 14
3,167 Views
0
CheenuSing
Community Champion
Community Champion
In response to antoniofallucca

‎08-21-2017 08:17 PM

Hi @antoniofallucca

 

 

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!
Message 6 of 14
4,833 Views
antoniofallucca
Helper I
Helper I
In response to CheenuSing

‎08-22-2017 01:58 PM

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?

Message 7 of 14
4,801 Views
0

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

View All