Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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.
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?
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!
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |