Forum Discussion

antoniofallucca's avatar
antoniofallucca
Icon for Helper I rankHelper 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's avatar
    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's avatar
    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.

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft 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

antoniofallucca's avatar
antoniofallucca
Icon for Helper I rankHelper I
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.