Forum Discussion

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...

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.

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.

antoniofallucca

Helper I

9 years ago

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

Anonymous
Not applicable
9 years ago
Hi antoniofallucca

Can you share some data of the StaffUnit Table on one drive and provide the link.

Cheers

CheenuSing
- antoniofallucca
  Helper I
  9 years ago
  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?
v-jiascu-msft
Microsoft Employee
9 years ago
Hi antoniofallucca,

My example works in non-continuous data. Could you please share a sample?

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