Visually separate all filters applied with a comma

[Anonymous]

Hi,

I'm trying to add a box which displays all the filters which have been applied to a certain report.

So far I've come to this sentence:

Filters = IF(
    (IF(ISFILTERED('Top 250'[IMDb Rating]),"Rating",BLANK()))
    &
    (IF(ISFILTERED('Top 250'[Rank]),"Rank",BLANK()))
    &
    (IF(ISFILTERED('Top 250'[Title]),"Title",BLANK()))
         
    =BLANK(),"",

    COMBINEVALUES(", ",
     (IF(ISFILTERED('Top 250'[IMDb Rating]),"Rating",Blank()))
    ,
    (IF(ISFILTERED('Top 250'[Rank]),"Rank",Blank()))
    ,
    (IF(ISFILTERED('Top 250'[Title]),"Title",Blank()))
    )
    )

It works well when there are no filters applied. But when one is applied it generates blanks and puts in all the commas.

The table and coloumns are just from IMDB top 250.

Do you have any suggestions on how to solve this issue so the commas aren't visible until you apply another filter?

[Anonymous · Accepted solution]

Hi guys

I read some other posts in the Forums and got inspiration to make this sentence:

Filters applied =

IF(ISFILTERED(Account[Account]);
"-Account: "& CONCATENATEX(VALUES(Account[Account]);Account[Account];", ")&
"
"
&
"
"
)

&IF(ISFILTERED('Application'[Application]);
"-Application: "& CONCATENATEX(VALUES('Application'[Application]);'Application'[Application];", ")&
"
"
&
"
"
)

&IF(ISFILTERED('Central Organization'[Business Division]);
"-Business Division: "& CONCATENATEX(VALUES('Central Organization'[Business Division]);'Central Organization'[Business Division];", ")&
"
"
&
"
"
)

Don't mind the wierd line breaks. I'm connected directly to the cube which doesn't support Unicodes.

It actually works quite well when wrap text is applied and it's put in a table and also includes the values which have been filtered.

Looks like this:

[Anonymous · Accepted solution]

Hi guys

I read some other posts in the Forums and got inspiration to make this sentence:

Filters applied =

IF(ISFILTERED(Account[Account]);
"-Account: "& CONCATENATEX(VALUES(Account[Account]);Account[Account];", ")&
"
"
&
"
"
)

&IF(ISFILTERED('Application'[Application]);
"-Application: "& CONCATENATEX(VALUES('Application'[Application]);'Application'[Application];", ")&
"
"
&
"
"
)

&IF(ISFILTERED('Central Organization'[Business Division]);
"-Business Division: "& CONCATENATEX(VALUES('Central Organization'[Business Division]);'Central Organization'[Business Division];", ")&
"
"
&
"
"
)

Don't mind the wierd line breaks. I'm connected directly to the cube which doesn't support Unicodes.

It actually works quite well when wrap text is applied and it's put in a table and also includes the values which have been filtered.

Looks like this:

[ricardocamargos · Accepted solution]

Hi @Anonymous,

Sorry about the last solution, I missed that. I'm glad you have found the solution.

Also, I've updated my code:

Filtro =
VAR _Filter = CONCATENATE(
CONCATENATE(
IF(ISFILTERED(Table1[DADO_1]); "Dados 1;"; BLANK());
IF(ISFILTERED(Table1[DADO_2]); "Dados 2;"; BLANK()));
IF(ISFILTERED(Table1[DADO_3]); "Dados 3;")
)
RETURN IF(ISBLANK(_Filter); ""; LEFT(_Filter; LEN(_Filter) - 1))

[Anonymous]

Hi @ricardocamargos

That is beautiful.

Your new code is actually the best solution for the initial issue.

Thank you very much.

[ricardocamargos]

Hi @Anonymous,

I think the COMBINEVALUES is not the best option to use in this situation, you are gonna have problem with the separator when the value is blank, it will show something like " Value;;;".

Please, try this code:

VAR _Filter = CONCATENATE(
CONCATENATE(
IF(ISFILTERED(Table1[DADO_1]); "Dados 1"; BLANK());
IF(ISFILTERED(Table1[DADO_2]); ";Dados 2"; BLANK()));
IF(ISFILTERED(Table1[DADO_3]); ";Dados 3")
)
RETURN IF(ISBLANK(_Filter); ""; _Filter)

[Anonymous]

Hi

I see your point. But this will result in the output: ,Dados2, Dados 3
If you choose the second and third filter without choosing the first.

Appreciate the answer though.