Forum Discussion

antoniofallucca

Helper I

9 years ago

Solved

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

v-jiascu-msft

9 years ago

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

Best Regards!

Dale

antoniofallucca
9 years ago
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

antoniofallucca
Helper I
9 years ago
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
- v-jiascu-msft
  Microsoft Employee
  9 years ago
  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] )
  
  Best Regards!
  Dale
  - antoniofallucca
    Helper I
    9 years ago
    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.

Forum Discussion

Dax Measure to select multiple filters from a table

13 Replies

Related content

Measure with multiple filters on same field

Filter required for Multiple measures for combo chart

Dynamic measure filtering

Multiple Years Filter

Multiple measures in matrix by year