Forum Discussion

Ben83's avatar
Ben83
Icon for Advocate IV rankAdvocate IV
10 years ago
Solved

Text field to show the selected filter

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...
Need Help
  • konstantinos's avatar
    konstantinos
    10 years ago

     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

     

     

konstantinos's avatar
konstantinos
Icon for Memorable Member rankMemorable Member
10 years ago

 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

 

 

  • Ben83's avatar
    Ben83
    Icon for Advocate IV rankAdvocate IV
    10 years ago

    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?

    • konstantinos's avatar
      konstantinos
      Icon for Memorable Member rankMemorable Member
      10 years ago

      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"
      )
    • kavyasree's avatar
      kavyasree
      Regular Visitor
      10 years ago
      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
  • MP_123's avatar
    MP_123
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    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's avatar
      konstantinos
      Icon for Memorable Member rankMemorable Member
      10 years ago

      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?

       

       

      • MP_123's avatar
        MP_123
        Icon for Microsoft Employee rankMicrosoft Employee
        10 years ago

        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!

  • Jayanth's avatar
    Jayanth
    Regular Visitor
    8 years ago

    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's avatar
      Pandu
      New Member
      8 years ago

      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's avatar
        SamirAlis
        New Member
        8 years ago

        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!