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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
javi0unavailabl
Resolver II
Resolver II

Show active filters.

Hello.

 

I have a very extensive report with a lot of slicers.

 

What I need is something that shows me the filters applied (or slicers) at the moment. It dynamically should change when a slicer is (des)selected.

 

Since Power BI does not offer this feature, I need a workaround.

 

What I achieved:

In another smaller report, I created one measure for each column that returns a text if the column ISFILTERED:

  

M_checkfilter = IF(ISFILTERED(Table[Column]);"Filter is Active";"")

This measure is used within a text box card visual and situated above a slicer that filters the previous column referenced. Then, when the slicer is actived, the text appears (color: red, font size: medium-high) so the user can easily show what filters are actived.

 

Well, it works, but in an extensive report it comes unmanageable creating one measure and text box for each column. I'm looking for something more centralized.

 

I hope you can come up with something.

 

Thank you.

 

----

Javi

1 ACCEPTED SOLUTION
javi0unavailabl
Resolver II
Resolver II

Ok... I achieved a solution... it is not very orthodox but it works fine... Very tedious to write...

 

It consists in a Measure of more than 120 lines (because the big number of columns and tables) with a text result that I can insert into a Card visualization or a table for better viewing.

 

The approximate formula syntax of the measure is like this:

 

_Active filters_ = 
"FilterSubsection:"
&IF(ISFILTERED(Table1[Column1]);
    UNICHAR(10)&"- StringColumnName: "& CONCATENATEX(VALUES(Table1[Column1]);Table1[Column1];", "))
&IF(ISFILTERED(Table1[Column2])
... (etc)

Where u see Table1 you can refer to Table2, Table3 an so on.

UNICHAR(10) is a newline character and & simbol is to concatenate strings.

 

Hope this serves to someone in the future and really hope that the PBI team include a feature to achieve this easier.

 

Thanks.

View solution in original post

14 REPLIES 14
KostaMadorsky
Regular Visitor

I know it's an old topic, but just wanted to share an awesome video that shown how you can generate the filtersContext measure just in 2 clicks in DAX Studio:

https://www.youtube.com/watch?v=kccmckYOmbw&ab_channel=SQLBI

 

In my case, it generated a 1600 line measure 🙂

 

Then just drop it onto a Table visual and you will see a list of applied filters on specific table or even all of the tables.

 

Super helpful when you are trying to debug some DAX measure or if you just want to show to end user all the filters applied in a tooltip.

This is really a great hint! Thank you!

javi0unavailabl
Resolver II
Resolver II

Ok... I achieved a solution... it is not very orthodox but it works fine... Very tedious to write...

 

It consists in a Measure of more than 120 lines (because the big number of columns and tables) with a text result that I can insert into a Card visualization or a table for better viewing.

 

The approximate formula syntax of the measure is like this:

 

_Active filters_ = 
"FilterSubsection:"
&IF(ISFILTERED(Table1[Column1]);
    UNICHAR(10)&"- StringColumnName: "& CONCATENATEX(VALUES(Table1[Column1]);Table1[Column1];", "))
&IF(ISFILTERED(Table1[Column2])
... (etc)

Where u see Table1 you can refer to Table2, Table3 an so on.

UNICHAR(10) is a newline character and & simbol is to concatenate strings.

 

Hope this serves to someone in the future and really hope that the PBI team include a feature to achieve this easier.

 

Thanks.

@javi0unavailabl 

 

Thks, it is still helping after all this time!!! 

First of all, thanks for the amazing post, it was really usefull for me.  

 

Although it works perfectly when i applied the sintax: 

Filter activ =
"Filter active:"
&IF(ISFILTERED(event[Sex])
;UNICHAR(10)&"Sex: "& CONCATENATEX(VALUES(event[Sex]);event[Sex];", "))
&IF(ISFILTERED(event[age])
;UNICHAR(10)&"Ages: "& CONCATENATEX(VALUES(event[age]);event[age];", "))
&IF(ISFILTERED(eventoysujeto[Date])
;UNICHAR(10)&"Date: "& CONCATENATEX(VALUES(event[Date]);eventoysujeto[Date];", "))

 

It shows me something (perfect) like:

 

Filter active:

Sex: Male.

Age: 85, 88, 73, 55, 80, 73, 89, 72, [...] and so on.

Date: 4/3/2017, 2/21/2018  [...] and so on.

 

My problem is that i do have dozens of different ages and dates.  I do want to obtain something like this:

Filter active:

Sex: Male.

Age min: 56

Age max: 90

fDate: 4/3/2017

lDate: 12/31/2017

 

Or if possible: 

Filter active:

Sex: Male.

Age: 56 to 90.

Date: 4/3/2017 to 12/31/2017.

 

May someone help me?

 

Thanks a lot,

Angel

@areymejias For that, you can do something like this:

 

[...]
&IF(ISFILTERED(event[age])
;UNICHAR(10)&"Ages: "& MIN(event[age]) & " - " & MAX(event[age]);", "))
[...]

Tell me if this is what you are looking for.

 

Regards.

 

 

@javi0unavailabl Thanks a lot for the fast response and sorry for the dealy but i was out of office.

 

I did tried hundreds of changes before i post this, your sentence "should" do exactly what i am looking for but it does not work, it seems to appears and error with & I have tried, just with MIN, or with MAX, with less text, with more parenthesis and hundreds of combinations, but it does not work, i am out of ideas. If you have any i would love to hear them.

 

Thanks in advance,

Angel

I don't know... you said about syntax error with &...    if there is nothing before the formula above posted, then remove the first & in "

&IF(ISFILTERED [...]

", & it's used to concatenate text

 

Could you post the error you are getting?

 

 

Hi Everyone,

 

I tried using the method to show filters, it works fine but I am unable to display filters in different lines (Line break) when I am viewing the measure in a card.

I am not creating measure in Query Editor but on report view itself. Please let me know hoe we can insert line breaks between the filters

string = "a" & UNICHAR(10) & "a"

 

UNICHAR(10) is the line break char

 

Regards

Thank you very much for sharing.

It works perfect and I have to say: it is not that tedious and simply to achieve.

Two days ago a customer asked me to have "Active filter" text solution on a report and this solution fits perfectly. Thaks you one more time.

Thak you very much for sharing. It works perfect and I have to say: it is not that tedious and simply to achieve.

Two days ago a customer ask to have "Active filter" text solution and this solution fits perfectly. Thaks one more time.

v-huizhn-msft
Employee
Employee

Hi @javi0unavailabl,

After test, the measure and column are not supported in text box, you can create a card visual to display the measure. You can review the following feature request and vote it, you also create an idea here.

Description to the Calculated Columns & Measures

Best Regards,
Angelia

Hi @v-huizhn-msft

 

I'm sorry. I meant Card visual instead text box.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.