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.

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