Forum Discussion
Helper IDax Measure to select multiple filters from a table
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.
Helper II 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
v-jiascu-msftMicrosoft Employee
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
- antoniofallucca
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.
- antoniofallucca
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_unitsI can't seem to figure it out can anyone help?
Thank you,
Antonio
- antoniofallucca
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_unitslocation_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