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

[Anonymous]

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.

[Anonymous]

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

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]

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]

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?

[AlB]

@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]

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.

[AlB]

@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  

[Anonymous]

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.

[AlB · 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  

[Anonymous]

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]

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