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.
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 =
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
Helper IHere 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 |