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
spencer_g
Helper I
Helper I

Default to Un-populated Slicer

Hello and thanks in advance for any help you may have to offer!

 

I have a client that wants the ability to filter the visuals on a page by multiple different metrics, for example lets say Date, City, and Airline. So, I created 3 separate chicklet slicers, one for each metric. The client has now asked for a visual at the top of the page that depicts the selected/filtered by metrics. For instance, if I used the chicklet slicers to filter by 2014; Atlanta, NYC, and Chicago; and Delta, the client wants a visual at the top that indicates that those are the current metrics being shown in all of the charts on the page. 

 

I believe the Smart Filter is the best visual for this, however, I need the visual to by default appear blank or un-populated and only depict a metric once a chicklet slicer has been selected. Unfortunately, the Smart Filter defaults to all metrics depicted and filters down when the chicklet is selected. 

 

Does anyone know of a forumla or another way to force the Smart Filter (or any other visual) to appear un-populated or blank by default? 

 

Any help would be greatly appreciated, and if further clarification is needed from me please let me know. Thanks in advance!

2 ACCEPTED SOLUTIONS
chrisu
Responsive Resident
Responsive Resident

It sounds like you don't want this to be a slicer (you already have those), more of a dynamic display of selected slicer values.  

 

I have done this using IF and ISFILTERED to determine if date (for example) is filtered.  If true, then I use CONCATENATEX to bring together the distinct selected values in that column, then use a regular CONCATENATE to combine the results across columns.  You can also wrap this in another IF to display a blank "" if no filters are selected.

 

See https://blog.crossjoin.co.uk/2016/04/25/dynamic-chart-titles-in-power-bi/ for more information.  

 

View solution in original post

chrisu
Responsive Resident
Responsive Resident

Hmmm...are you using VALUES(table[column]) as the first argument in CONCATENATEX?  That should give you the distinct values.  If that doesn't work, can you provide your formula?

View solution in original post

6 REPLIES 6
chrisu
Responsive Resident
Responsive Resident

It sounds like you don't want this to be a slicer (you already have those), more of a dynamic display of selected slicer values.  

 

I have done this using IF and ISFILTERED to determine if date (for example) is filtered.  If true, then I use CONCATENATEX to bring together the distinct selected values in that column, then use a regular CONCATENATE to combine the results across columns.  You can also wrap this in another IF to display a blank "" if no filters are selected.

 

See https://blog.crossjoin.co.uk/2016/04/25/dynamic-chart-titles-in-power-bi/ for more information.  

 

Hi Chrisu, 

 

Thanks again for your message, you have most definitely put me on the right path. Using your suggested funtions, I've been able to create a dynamic multi-row card. However, I am running into one snag that I'm hoping you can talk me through. 

 

When using the CONCATENATEX function, instead of getting a distinct result I get the same result over and over. For example, let's say I filter by the cities of Atlanta and NYC, and the Airline of Delta. The visual I get is "Atlanta, Atlanta, Atlanta, Atlanta, Atlanta, Atlanta, etc...." | "Delta, Delta, Delta, Delta, etc...." , and what I need is the dinstinct result. So, "Atlanta, NYC" | "Delta"

 

Do you have any thoughts on what I'm doing wrong and/or how I can correct it?

 

Thanks again!

chrisu
Responsive Resident
Responsive Resident

Hmmm...are you using VALUES(table[column]) as the first argument in CONCATENATEX?  That should give you the distinct values.  If that doesn't work, can you provide your formula?

I figured out my error:

 

= IF(ISFILTERED(Dim_Flights[City]), (CONCATENATEX(VALUES(Dim_Flights[City]), Dim_Flights[City], ",")), IF(BLANK(), ""))

 

Worked like a charm!

 

Thank you again. You're a lifesaver! 

Here's what I had originally:

 

= IF(ISFILTERED(Dim_Flights[City]), (CONCATENATEX(Dim_Flights, [City], ",")), IF(BLANK(), ""))

 

I tried adding VALUES(table[column]) but that broke it.

 

= IF(ISFILTERED(Dim_Flights[City]), (CONCATENATEX(VALUES(Dim_Flights[City]), Dim_Flights, [City], ",")), IF(BLANK(), ""))

 

Thanks for all your help!

 

Thanks, Chrisu, I think you are exactly right that what I am looking to create is a dynamic display. I'll check out the link you provided and see what I can come up with. Based on what you wrote though it sounds like you're suggesting exactly what I was looking for. 

 

Thanks! 

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.