How to make one slicer for several columns in a table?

[Shelley]

Looking for help with this, please. I have SUM and COUNT calculations that I need to perform over several different time frames. That is, a customer can select whether they want to look at the last 30 days, the last 90 days or the last 365 days to see what the sums or counts were for each of those time frames. As a start, I have created three corresponding flag fields in my calendar table that look at NOW and then present 1 if the date is within the respective timeframe (e.g. last 30, 90 or 365 days), and 0 if not.

On the other end, I have created a Slicer table with three fields, Last 30-Days, Last 90-Days and Last 365-Days. There is no relationship between this table and any other tables in the model.

Now, here's the tricky part. When a user has selected one of the timeframes in the slicer, like Last 30-Days, how can I calculate a sum or count of incidents in the fact table, based on the 30-Day Flag = 1 in the calendar table? 

[Shelley · Accepted solution]

I'm all ears on help because I am new to Power BI and want to be able to do a great job with it. So I really appreciate the insights. 

I created a complicated nested IF statement, which appears to work, but I wonder if there is a better way that also runs faster as this is probably slow.  Here's what I did, along with a slicer table example and a simplified calendar example.

Audit Count = IF(CONTAINS('Audit Complete Slicer', [Audit CompleteTimeframe], "Last 30-Days"),

    CALCULATE(COUNT('audits'[enddate]), 'Daily Calendar'[Last 30-Days]=1),

        IF(CONTAINS('Audit Complete Slicer',[Audit CompleteTimeframe], "Last 90-Days"),

            CALCULATE(COUNT('audits'[enddate]), 'Daily Calendar'[Last 90-Days]=1),

                IF(CONTAINS('Audit Complete Slicer', [Audit CompleteTimeframe], "Last 365-Days"),

                    CALCULATE(COUNT('audits'[enddate]), 'Daily Calendar'[Last 365-Days]=1),

    BLANK())))

Audit CompleteTimeframe	Sort
Last 30-Days	1
Last 90-Days	2
Last 365-Days	3

Simplified Calendar

Date      Last 30-Days     Last 90-Days      Last 365-Days

Day 1                1                            1                              1

Day 2                0                            1                              1

Day 3                0                            1                              1    

Day 4                0                            0                              1

Day 5                1                            1                              1

Day 6                0                            0                              0

[Greg_Deckler]

I would re-engineer this to a single column that puts "30 days", "90 days" or "last 365 days" and "other" as an end result. Then, I would just use that column as my slicer.

[Shelley]

Thanks for the input. How would you perform the sum or count functions specifically then? Events that are in the Last 30-Day window would also have to be included when Last 90-Days are selected. Likewise, when Last 365-Days is selected, all three categories would have to be included. Do you have a sample formula for the 365-Days scenario for say, counting a field called "Events? when this category is selected in the slicer. Maybe I'm making this too complicated, but I have even more timeframes I have to use as well. I thought that if I could identify a clear approach, I could use it across the other time frames as well. Thanks for your help!

[Greg_Deckler]

Probably use measures for those calculations and then employ the disconnected table trick to invoke each of my custom count measures.

[TomMartens]

Hey,

just considering this

• create a table "timeframe" that just stores your timeframes
• create a table "timeframe - dates", that contains the dates for each timeframe
• create a relationship timeframe (one side) -> timeframe_dates (many side) with filter direction single
• create a relationship between your calendar table (one side) and "timeframe - dates" (many side) with filter direction both

Guess this could work, will try it myself tomorrow

Regards

[Shelley · Accepted solution]

I'm all ears on help because I am new to Power BI and want to be able to do a great job with it. So I really appreciate the insights. 

I created a complicated nested IF statement, which appears to work, but I wonder if there is a better way that also runs faster as this is probably slow.  Here's what I did, along with a slicer table example and a simplified calendar example.

Audit Count = IF(CONTAINS('Audit Complete Slicer', [Audit CompleteTimeframe], "Last 30-Days"),

    CALCULATE(COUNT('audits'[enddate]), 'Daily Calendar'[Last 30-Days]=1),

        IF(CONTAINS('Audit Complete Slicer',[Audit CompleteTimeframe], "Last 90-Days"),

            CALCULATE(COUNT('audits'[enddate]), 'Daily Calendar'[Last 90-Days]=1),

                IF(CONTAINS('Audit Complete Slicer', [Audit CompleteTimeframe], "Last 365-Days"),

                    CALCULATE(COUNT('audits'[enddate]), 'Daily Calendar'[Last 365-Days]=1),

    BLANK())))

Audit CompleteTimeframe	Sort
Last 30-Days	1
Last 90-Days	2
Last 365-Days	3

Simplified Calendar

Date      Last 30-Days     Last 90-Days      Last 365-Days

Day 1                1                            1                              1

Day 2                0                            1                              1

Day 3                0                            1                              1    

Day 4                0                            0                              1

Day 5                1                            1                              1

Day 6                0                            0                              0

[v-shex-msft · Accepted solution]

Hi @Shelley,

Perhaps you can try to use below formula:

Audit Count=
var select=IF(HASONEVALUE('Audit Complete Slicer'[Audit CompleteTimeframe]),VALUES('Audit Complete Slicer'[Audit CompleteTimeframe]),BLANK())
return
Switch([Audit CompleteTimeframe],
"Last 30-Days",CALCULATE ( COUNT ( 'audits'[enddate] ), 'Daily Calendar'[Last 30-Days] = 1 ),
"Last 90-Days",CALCULATE ( COUNT ( 'audits'[enddate] ), 'Daily Calendar'[Last 90-Days] = 1 ),
"Last 365-Days",CALCULATE ( COUNT ( 'audits'[enddate] ), 'Daily Calendar'[Last 365-Days] = 1),
BLANK ())

If above not help, please share some sample data to test.

Regards,

Xiaoxin Sheng

[Shelley]

Hi @v-shex-msft

Thank you for the idea. I had to make one change and then your idea also worked as did mine above. At the SWITCH I believe it should say:

RETURN
SWITCH(Select,

Thanks again!

[TomMartens]

Hey,

I guess I would do something like this (adjust all your measures like this

add a

• variable "timeframeslicer" that stores the selection of your timeframe-slicer using the new function SELECTEDVALUE with a default of 0 if there is no selection,
• a variable that stores the enddate NOW
• a variable that stores the startdate using SWITCH and an expression like NOW - 30
• a table variable using DATESBETWEEN('calendartable'[date], startdate, enddate)

and then adjust your measure like this
CALCULATE(yourcurrentexpression, thetablevariable)

Hope this helps

[Shelley]

Thanks for the input! It seems like this proposed solution is recreating the time flags I already developed in the calendar table. Is there not a formula I can use to utilize the flags?