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

 

 

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!

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

        never mind , thanks :) i succeeded

    • m1032499's avatar
      m1032499
      Frequent Visitor
      10 years ago

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

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

        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],
    ", "
)