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.

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

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi antoniofallucca,

 

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

 

Best Regards!

Dale

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