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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to get multiple values selected in a slicer and store them in a nX1 table

Hi,

 

I am a PowerBI noob. I have a slicer where users can select multiple values. Say, user selectes Shoes, Electronics, Books in the slicer.

 

Question 1: How do I store the values "Shoes, Electronics, Books" in a 3*1 table for later use.

 

Question 2: I have been able to concatenate Shoes, Electronics, Books  into a measure. Alternatively, how can I unconcatenate this measure to get the 3*1 table.

 

Many thanks in advance.

1 ACCEPTED SOLUTION

@Anonymous 

a quick and dirty solution. See the attached file also. It might need refining depending on whatr you exactly want in different scenarios. I had assumed there was only one row per customer

ShowMeasure2 = 
VAR CatCostumer_ =
    CONCATENATEX (
        CALCULATETABLE ( DISTINCT( Customer[Category] ); ALL(Customer[Category]));
        Customer[Category];
        ", "
    )
VAR AuxTable_ =
    ADDCOLUMNS (
        DISTINCT ( 'Merchandise Category'[Category] );
        "Found"; SEARCH ( 'Merchandise Category'[Category]; CatCostumer_;; 0 )
    )
VAR AllFound_ =
    COUNTROWS ( AuxTable_ )
        = SUMX ( AuxTable_; INT ( [Found] > 0 ) )
RETURN
 INT ( AllFound_ )

 

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  Datanaut

 

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

Hello @AlB, I'm getting unexpected result see below image

Audience Filter.png

 

I have audience filter, when I select "Architect" value, result gives me "Customers - Architect/Business" and "Field Architects" value in result. So how I fix this?

ShowMeasure =
VAR CatCostumer_ =
CONCATENATEX (
CALCULATETABLE ( DISTINCT( CxPEvents[Whoaretheaudience] ), ALL(CxPEvents[Whoaretheaudience])),
CxPEvents[Whoaretheaudience],
", "
)
VAR AuxTable_ =
ADDCOLUMNS (
DISTINCT ( 'Audience Master'[Title] ),
"Found", SEARCH ( 'Audience Master'[Title], CatCostumer_,, 0 )
)
VAR AllFound_ =
COUNTROWS ( AuxTable_ )
= SUMX ( AuxTable_, INT ( [Found] > 0 ) )
RETURN
INT ( AllFound_ )

 

AlB
Super User
Super User

Hi @Anonymous 

Some sample data would be helpful. I'm not sure I understand what you mean but if you do this: 

DISTINCT(SlicerTable[SlicerColumn])

you'll have a one-column table with the values selected in the slicer.  

Anonymous
Not applicable

Hi @AIB, Thanks for your reply. But the DISTINCT statement is not working. It is returning ALL the distinct values in my slicer table and not the ones that are specifically selected in the slicer visualization. I want to retrieve only those values that have been selected in the slicer visual. How do I do that?

@Anonymous 

It should work but tt needs to be somewhere under the effect of the slicer of course. Where are you using it? Can you share the pbix?

If you are creating a new table with it it won't work because tables are static.   

Anonymous
Not applicable

PFA the pbix here: https://drive.google.com/open?id=1zjahl6K3eEuO5pxM2rnAmS4vlYyH4EDN

I don't have permissions to upload it here directly.

 

I want my filter selection to be available to me for text search. What I am actually tring to do is this: I want to select "Books, Electronics and Shoes" and want to see Cust 5 in the table. I want to do an AND search and NOT the default Power BI OR search.

@Anonymous 

Create this measure and place it in the visual filter; select to show when it is 1. See attached file

ShowMeasure = 
VAR CatCostumer_ = SELECTEDVALUE ( Customer[Category] )
VAR AuxTable_ =
    ADDCOLUMNS (
        DISTINCT('Merchandise Category'[Category] );
        "Found"; SEARCH ( 'Merchandise Category'[Category]; CatCostumer_;; 0 )
    )
VAR AllFound_ = COUNTROWS ( AuxTable_ ) = SUMX ( AuxTable_; INT ( [Found] > 0 ) )
RETURN
    INT ( AllFound_ )

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  Datanaut

  

Anonymous
Not applicable

Thank you @AIB. This worked for all cases except one which is if I select both "Beauty" and "Electronics" in my filter, it should show Cust 2 as that customer has bought both these. But in the table visual it is showing blank and not Cust 2. Can you pleaselet me know how can I show Cust 2 here.

@Anonymous 

a quick and dirty solution. See the attached file also. It might need refining depending on whatr you exactly want in different scenarios. I had assumed there was only one row per customer

ShowMeasure2 = 
VAR CatCostumer_ =
    CONCATENATEX (
        CALCULATETABLE ( DISTINCT( Customer[Category] ); ALL(Customer[Category]));
        Customer[Category];
        ", "
    )
VAR AuxTable_ =
    ADDCOLUMNS (
        DISTINCT ( 'Merchandise Category'[Category] );
        "Found"; SEARCH ( 'Merchandise Category'[Category]; CatCostumer_;; 0 )
    )
VAR AllFound_ =
    COUNTROWS ( AuxTable_ )
        = SUMX ( AuxTable_; INT ( [Found] > 0 ) )
RETURN
 INT ( AllFound_ )

 

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  Datanaut

 

Anonymous
Not applicable

This is very useful. I have similar issue where I wanted  your help . I have below link to the PBIX file as community forum is not letting me attach the file. 

 

There are two tabs each explaning the problem. I have resolved first tab (task 1) but second task I am struggling with.  Details can be found at 

https://drive.google.com/file/d/1RPYXjMx_MfVTDUa8pSI4hzp5_x0kOTLy/view?usp=sharing

 

Actual quesiton is also posted at

https://community.powerbi.com/t5/Desktop/Slicer-with-position-of-choice-to-see-what-user-has-selecte...

 

Kind regards

 

 

 

Anonymous
Not applicable

@AIB, thank you for this. This works for all my use cases now !!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.