Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Shelley
Continued Contributor
Continued Contributor

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

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? 

2 ACCEPTED SOLUTIONS
Shelley
Continued Contributor
Continued Contributor

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

View solution in original post

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

9 REPLIES 9
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Shelley
Continued Contributor
Continued Contributor

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!

 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Shelley
Continued Contributor
Continued Contributor

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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Shelley
Continued Contributor
Continued Contributor

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
Super User
Super User

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Shelley
Continued Contributor
Continued Contributor

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.