Text field to show the selected filter

[Ben83]

I have created a report for the delivery reliability of our company. In this report I imported a long excel list with all of our delivery positions of a whole year. Every single delivery positions is rated by tolerances for our delivery date and our delivery quantity.

Currently it is possible to filter the report by selecting one, many or all of our customers to show the result of our delivery performance for them.

Now I would like to have a text field or what ever in which the word "all" is listed when the report shows me the result for all customers (no filter selected) and the name of the customer when the report is filtered by one or many customer.

Is it possible? I hope someone of you can help me to fix my problem!

[Anonymous]

Hello everyone,

I still have the problem and I used your solution which is working need a small enhancement as described below,

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Default-message-to-be-written-when-all-record...

[konstantinos · Accepted solution]

 You can try a measure that return text. You need to add it in a table , matrix or card visual.

1. If you have a client column/table with unigue clients you can try this

Clients Filtered =
IF (
    HASONEVALUE ( Clients[ClientName] );
    VALUES ( Clients[ClientName] );
    "ALL"
)

2. If not you, you filter from fact table or other this option should work

Clients Filtered 2 =
IF (
    COUNTROWS ( VALUES ( Sales[Client] ) ) = 1;
    VALUES ( Sales[Client] );
    "ALL"
)

Hope that helps

[Jayanth]

Hi,

 If i choose Age 30,40 and 50 it should display as the same in the text field and if i choose Select ALL it should be  display as 'ALL'  Below code only work for single select but not for Multiselect of Age from Drop down, Please help me

AFILTER = If(HASONEFILTER('UVN_CNSMR_CORE MSTR_CNSMR_PRFL'[AGE]),
VALUES ('UVN_CNSMR_CORE MSTR_CNSMR_PRFL'[AGE]),"ALL")

[Pandu]

Hi Jayanth,

Follow the below stpes to display multi select prompt values in text field.

Iam giving example based on the year prompt in my scenario

 i) Create a measure (Text Filed)with the column Year

Text Field = 
IF( ISBLANK(
CONCATENATEX (
 VALUES ( Sheet6[Year] ),
 Sheet6[Year],
 ", "
)), BLANK()," In " & 
CONCATENATEX (
 VALUES ( Sheet6[Year] ),
 Sheet6[Year],
 ", "
))

ii) Use this measure for a CARD Visualization and OFF the Category Label Property.

Thank You,

Pandu.

[SamirAlis]

@PanduThank you for the brilliant solution!

I basically combined with another if for situations that may need something more condensed if all selections are checked:

Text Field = 
IF(ISFILTERED(Sheet6[Year]),

IF( ISBLANK(CONCATENATEX (VALUES (Sheet6[Year]), Sheet6[Year], ", ")), BLANK(), 
CONCATENATEX (VALUES (Sheet6[Year]), Sheet6[Year], ", "))

,"ALL")

I've replaced the fields I used with the fields Pandu referenced. For my situation, this worked perfectly when choosing selections in a drop down list of clients.

Thank you!

[MP_123]

@konstantinos

hi!

i tried to do what you adviced but it didn't work for me 😞

i have a slicer, i want the user to choose one (only one) category from the list and the to show it on text field.

it shows me "all" all the time when i trying your code.

thank you very much for your help!

[konstantinos]

@MP_123 which one didn't work 1,2 or 3 version. Now I see that 3 don't work, it should be similar to 

Clients Filtered 3 =
SWITCH (
 TRUE;
 AND(ISFILTERED ( Client[ClientName] );COUNTROWS ( VALUES ( Client[ClientName] ) ) )= 1; VALUES ( Client[ClientName] );
 AND(ISFILTERED ( Client[ClientName] );COUNTROWS ( VALUES ( Client[ClientName] ) )) > 1; CONCATENATE ( COUNTROWS ( VALUES ( Client[ClientName] ) ); " Clients" );
 "ALL"
)

The other two one should work fine. I think you sould try one of the first two and replace "ALL" with BLANK()

If it doesn't work can you write some more details regarding the tables involed as the formula also?

[Jayanth]

Hi,

I have to show 'ALL' as test field, when I select the drop down Age limit as Multi select like 30,50 and 60 it should show as the same or if I choose Select All it should display in text field as ALL.

I used the below code, it works only for Single select not for Multi select, please help me...

AFILTER = If(HASONEFILTER('UVN_CNSMR_CORE MSTR_CNSMR_PRFL'[AGE]),
VALUES ('UVN_CNSMR_CORE MSTR_CNSMR_PRFL'[AGE]),"ALL")

AFILTER = If(HASONEFILTER('UVN_CNSMR_CORE MSTR_CNSMR_PRFL'[AGE]),
    VALUES ('UVN_CNSMR_CORE MSTR_CNSMR_PRFL'[AGE]),"ALL")

[Anonymous]

@konstantinos This 3rd one works great, but needed a little tweak. For AND, it returns TRUE/FALSE and BI said, it cant compare with Integer (1), hence I updated that, and it gives desired results. Here is the updated one.

Area Selected = SWITCH(TRUE,AND(ISFILTERED(Survey_Data[iv. Operating Area]),COUNTROWS(VALUES (Survey_Data[iv. Operating Area]))=1), VALUES ( Survey_Data[iv. Operating Area] ),
 AND(ISFILTERED (Survey_Data[iv. Operating Area]),COUNTROWS(VALUES(Survey_Data[iv. Operating Area]))>1), CONCATENATE ( COUNTROWS ( VALUES ( Survey_Data[iv. Operating Area]) ), " Areas" ),"ALL")

However, it would be great to come up with a way to show all the selected values, separated by 'Comma'. 

---

[konstantinos]

@Anonymous

This was an old post - reply. There is an option to show all values seperate by commas (or whatever delimiter ) 

Can you try

Area Selected =
CONCATENATEX (
    VALUES ( Survey_Data[iv. Operating Area] ),
    Survey_Data[iv. Operating Area],
    ", "
)

[Anonymous]

@konstantinos - Thank you!! I am so glad that I joined the forum yesterday (should have done it long back). I updated the code and, it works like a charm. I will share it with team.

[konstantinos]

@Anonymous  Community is a great place, thanks also as this was my first post since a loong time - I will start posting & reading again..

[m1032499]

When I select more than one value from a particular filter,Iam able to  get  the number of values selected, but I want to display all the values selected .How to get that?

Thanks

[MP_123]

@konstantinos

it worked, thanks a lot!

how can i add two filters to one measure?

like "if.. hasonevalue" + "-" + "if.. has one value"

i don't want to separate it to two measure. i want the title of the repot to be "category - product" as chosen in the filter

thanks!

[foram_shah]

Can you send the exact solution that worked for you? I am trying to achieve the same thing. 

[MP_123]

never mind , thanks 🙂 i succeeded

[Ben83]

It works with both of your ideas. Thank you @konstantinos.

The only problem is when I set a filter with more than one customer there stands also "all". Do you have a idea to solve this problem?

[kavyasree]

Hi, I have tried with this code, but I am getting Error, Cant Compare text to string, And If I select 2 filtering options from my Slicer it is showing "All" If I need Single value which code need to use..?? Thanks

[konstantinos]

Just thinking & writing without testing.If not working a variation should work and sure is not optimal

Clients Filtered 3 =
SWITCH (
 ISFILTERED ( Client[ClientName] );
 COUNTROWS ( VALUES ( Client[ClientName] ) ) = 1; VALUES ( Client[ClientName] );
 COUNTROWS ( VALUES ( Client[ClientName] ) ) > 1; CONCATENATE ( COUNTROWS ( VALUES ( Client[ClientName] ) ); " Clients" );
 "ALL"
)