cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

@data_banshee 

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
Frequent Visitor

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_ )

 

Super User III
Super User III

Hi @data_banshee 

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.  

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?

@data_banshee 

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.   

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.

@data_banshee 

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

  

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.

@data_banshee 

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

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

 

 

 

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors