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,
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
Helper IHi 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.
- I need to calculate a numerator for all the days by hour (number of minutes used) by room in my fact table.
- I have already calculated this.
- 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.
- I have already calculated the total meaning no filter,
- Plus the year,
- The month and,
- One day.
- I cannot the calculation for more than one day to work.
- I have a standard date dimension table by day.
- 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.