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

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.

  • antoniofallucca's avatar
    antoniofallucca
    Icon for Helper I rankHelper I
    9 years ago

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

     

    1. I need to calculate a numerator for all the days by hour (number of minutes used) by room in my fact table.
    2. I have already calculated this.
    3. 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.
    4. I have already calculated the total meaning no filter,
    5. Plus the year,
    6. The month and,
    7. One day.
    8. I cannot the calculation for more than one day to work.
    9. I have a standard date dimension table by day.
    10. 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_nameroomstaff_begin_local_datestaff_begin_local_yearstaff_day_of_weekstaff_end_local_dttmstaff_episode_idstaff_patience_idstaff_provider_namestaff_provider_typestaff_total_minutesstaff_units_per_hourstaff_year_month
    Location 1Room 33/16/2016201643/16/201610000001100000Provider Name 1Type336112201603
    Location 1Room 33/16/2016201643/16/201611000001210000Provider Name 1Type375125201603
    Location 1Room 33/16/2016201643/16/201611100001221000Provider Name 1Type18894201603
    Location 1Room 33/24/2016201653/24/201611110001222100Provider Name 1Type372124201603
    Location 1Room 33/24/2016201653/24/201611111001222210Provider Name 1Type420140201603
    Location 1Room 33/24/2016201653/24/201611563801272018Provider Name 1Type88201603
    Location 1Room 33/29/2016201633/29/201611797001297670Provider Name 1Type369123201603
    Location 1Room 33/29/2016201633/29/201612030201323322Provider Name 1Type1175235201603
    Location 1Room 43/2/2016201643/2/201612263401348974Provider Name 1Type387129201603
    Location 1Room 43/22/2016201633/22/201612496601374626Provider Name 1Type16683201603
    Location 1Room 43/22/2016201633/22/201612729801400278Provider Name 1Type333111201603
    Location 1Room 43/22/2016201633/22/201612963001425930Provider Name 1Type18291201603
    Location 1Room 43/23/2016201643/23/201613196201451582Provider Name 1Type13065201603
    Location 1Room 43/23/2016201643/23/201613429401477234Provider Name 1Type24983201603
    Location 1Room 43/23/2016201643/23/201613662601502886Provider Name 1Type411137201603
    Location 1Room 43/23/2016201643/23/201613895801528538Provider Name 1Type23779201603
    Location 1Room 43/25/2016201663/25/201614129001554190Provider Name 1Type13065201603
    Location 1Room 43/25/2016201663/25/201614362201579842Provider Name 1Type12864201603
    Location 1Room 43/25/2016201663/25/201614595401605494Provider Name 1Type5656201603
    Location 1Room 43/25/2016201663/25/201699999999999991Provider Name 1Type1616201603
    Location 1Room 43/25/2016201663/25/201699999999999991Provider Name 1Type4824201603
    Location 1Room 43/25/2016201663/25/201620000002200000Provider Name 1Type17286201603
    Location 1Room 63/15/2016201633/15/201622000002420000Provider Name 1Type9045201603
    Location 1Room C013/28/2016201623/28/201622200002442000Provider Name 1Type7035201603
    Location 1Room C013/28/2016201623/28/201622220002444200Provider Name 1Type12261201603
    Location 1Room C013/28/2016201623/28/201623320002565200Provider Name 1Type5628201603
    Location 1Room C013/28/2016201623/28/201624006002640660Provider Name 1Type9447201603
    Location 1Room C013/28/2016201623/28/201624692002716120Provider Name 1Type8643201603
    Location 1Room C013/28/2016201623/28/201625378002791580Provider Name 1Type11256201603
    Location 1Room C013/28/2016201623/28/201626064002867040Provider Name 1Type25886201603
    Location 2Room RC013/7/2016201623/7/201626750002942500Provider Name 2Type2929201603
    Location 2Room RC013/7/2016201623/7/201627436003017960Provider Name 2Type2323201603
    Location 2Room RC013/8/2016201633/8/201628122003093420Provider Name 2Type6231201603
    Location 2Room RC013/8/2016201633/8/201628808003168880Provider Name 2Type3618201603
    Location 2Room RC013/10/2016201653/10/201629494003244340Provider Name 2Type3366201603
    Location 2Room RC013/10/2016201653/10/201630180003319800Provider Name 2Type98147201603
    Location 2Room RC013/10/2016201653/10/201630866003395260Provider Name 2Type80160201603
    Location 2Room RC013/14/2016201623/14/201631552003470720Provider Name 3Type2121201603
    Location 2Room RC013/15/2016201633/15/201632238003546180Provider Name 4Type4444201603
    Location 2Room RC013/15/2016201633/15/201632924003621640Provider Name 4Type9045201603
    Location 2Room RC013/21/2016201623/21/201633610003697100Provider Name 4Type11658201603
    Location 2Room RC013/22/2016201633/22/201634296003772560Provider Name 5Type2525201603
    Location 2Room RC013/24/2016201653/24/201634982003848020Provider Name 6Type2121201603
    Location 2Room RC013/28/2016201623/28/201635668003923480Provider Name 6Type1616201603
    Location 2Room RC013/29/2016201633/29/201636354003998940Provider Name 7Type2626201603
    Location 2Room RC013/29/2016201633/29/201637040004074400Provider Name 7Type2020201603
    Location 2Room RC013/29/2016201633/29/201637726004149860Provider Name 7Type3131201603
    Location 3Room D013/6/2016201613/6/201638412004225320Provider Name 7Type776194201603
    Location 3Room D013/8/2016201633/8/201639098004300780Provider Name 7Type28896201603
    Location 3Room D013/13/2016201613/13/201639784004376240Provider Name 8Type740185201603
    Location 4Room S013/10/2016201653/10/201640470004451700Provider Name 8Type279186201603
    Location 4Room S013/23/2016201643/23/201641156004527160Provider Name 8Type19497201603
    Location 4Room S013/25/2016201663/25/201641842004602620Provider Name 8Type20167201603
    Location 4Room S013/29/2016201633/29/201642528004678080Provider Name 9Type25886201603
    Location 4Room S013/10/2016201653/10/201643214004753540Provider Name 10Type9292201603
    Location 4Room S013/10/2016201653/10/201643900004829000Provider Name 10Type120180201603

     

    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.