Change column values based on slicer selection

Question

Using PowerBI desktop, I have created a small table (called TimeSelector), with three elements: Day, Week and Month

The idea is to use the content of this table to create a slicer with three options

Thus, selecting one of those options should change the way dates are used in tables. For instance, selecting Day would result in the following table:

While selecting Week would result in this:

Etc..

I have tried to write a new measure taking in account the selected slicer element, but it is not working:

DayWeekMonthSelection = IF(CONTAINS(TimeSelector;TimeSelector[DayWeekMonth];"Month");
                           MONTH(VALUES('uptime_downtime'[Uptime_date])))

This is only the first part of the formula, only testing the month option as a start.

Any idea on how to do this?

Answer 1

To offer another perspective:

The approach I take with this is to have a separate table in the database - containing meta data about the date, called date_lookup.

2 of the fields in this table are FirstDateOfMonth & FirstDateOfWeek.

Some of the other fields are lastDateOfMonth & LastDateOfWeek, also DayOfWeek.

By using these fields I can easily present visuals that are grouped by month or week.

Sure you can use functions to get this information, but functions can be platform dependant. If you're making a join to the date_lookup anyway - it's no more effort to get this info from there...

The main reason we need to store this meta data is our company Financial year is Jul - Jun. Therefore we need to have available the Correct FY - which is stored as a field in the date_lookup table. I also have fields in there identifying public holidays...

Answer 2

This is an interesting question, but I'm not sure how to do exactly what you are asking for, so I'll suggest an alternative. (Changing a measure based on a slicer selection isn't too difficult, but I'm not sure a good way to swap out a field/dimension.)

---

Instead of creating a separate table for your slicers, a different possible approach would be to create a date hierarchy. Often when you drag a date column into the rows or columns box it will automatically create a date hierarchy with Year/Quarter/Month/Date, but since you want week and not quarter, let's create one manually.

First, create a couple calculated columns for week and month. For example:

Month = FORMAT(uptime_downtime[Date], "mmm")
Week = WEEKNUM(uptime_downtime[Date])

Now right-click on the date on the fields, and choose New Hierarchy. It should look like this now:

Now drag the Month and Week columns onto Date Hierarchy and then rearrange them in the appropriate order:

Now you can use that hierarchy in a matrix and use the drill up and down buttons

to get the different groupings: