slicer with overlapping values

[Anonymous]

I have a fact table that looks like:

Week Key	Sale
1	10
2	1
3	20
4	5
5	5

I have a simple measure:

Total Sales = SUM('Fact'[Sale])

I want to filter Total Sales by a single-select slicer value that represents overlapping groups of weeks.  My groups / slicer values are 'Last Week' (corresponding to week 5 right now), 'Last 2 Weeks' (corresponding to weeks 5 and 4) and 'Last 4 Weeks' (corresponding to weeks 5, 4, 3, and 2).  Because 'Last 4 Weeks' values overlap 'Last 2 Weeks' and 'Last Week' (and 'Last 2 Weeks' overlaps 'Last Week'), I can't create a simple dimension table that joins to the fact table.  And, a multi-select slicer of the actual [Week Key] values is unacceptable.  The groupings need to be pre-defined for the user. 

My usual solution pattern is to create a disconnected table of the slicer values and build the grouping logic into a new measure, like the following:

Total Sales New =
VAR __group = SELECTEDVALUE('Slicer Table'[Group])

RETURN

    IF(

        __group = "Last Week"
        ,CALCULATE(

            [Total Sales]

            ,'Fact'[Week Key] = 5

        )

        ,IF(

            __group = "Last 2 Weeks"

            ,CALCULATE(

                'Fact'[Week Key] IN {5, 4}
            )

            ,IF(

                __group = "Last 4 Weeks"

                ,CALCULATE(

                    'Fact'[Week Key] IN {5, 4, 3, 2}

                )

            )

        )

    )

Even though this works, it seems very tedious (a lot of if-thens).  Is there a better way to do this?

[amitchandak · Accepted solution]

@Anonymous , What I am getting you have sorted out the slicer problem.  The order in Switch will give preference.

To deal with week best way is week Rank or week number. In case you year and week (2001801) you can create rank on that

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)  // You can use week No or Year week in place of start date
Last 4 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Last 2 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-2 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

[Greg_Deckler]

@Anonymous So yes, for starters use SWITCH instead. There is probably an even more elegant way but SWITCH is tons cleaner already.

Most elegant might be to have actual dates and use a relative date slicer.

Couldn't you just create a column in your table like this and use it in your slicer?

Column =
  SWITCH(TRUE(),
    [Week Key] = 5,"Last Week",
    [Week Key] IN {5,4},"Last 2 Weeks",
    [Week Key] IN {5,4,3,2},"Last 4 Weeks"
    "Older"
  )

[Anonymous]

@Greg_Deckler 

I will definitely use SWITCH in my new measure instead of nested IFs to make the code faster to type and easier to read.

Regarding the calculated column solution (as opposed to the disconnected table + switchable measure solution), if I'm understanding correctly, that won't work.  For example, when the user selects slicer value "Last 2 Weeks", the measure needs to filter to weeks 4 and 5.  If I use a calculated column, though, the "Last 2 Weeks" slicer value will only filter to [Week Key] = 4; it will incorrectly exclude [Week Key] = 5.  As far as I know, to achieve this, the mapping of [Group] to [Week Key] needs to be pushed into the measure definition (along with the help of a disconnected table that feeds the corresponding slicer).

[Greg_Deckler]

@Anonymous Oh, man, you are so right. Row can only have one value in a column, duh!! Apparently I've been at this too long today! Thanks for setting me straight!. I love disconnected tables BTW.

Here is an interesting example of one if you are interested. https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563