cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vinothkumar1990
Helper I
Helper I

Add multiple columns with one selection from slicer

Hi

 I have a below requirement in my project.

 

Req1 : I want to display the report dynamically based on the slicer selection. For example i have below Order and Customer tables, if i select the Order from slicer then Order Table visual should be displayed and if i select customer then customer table visual should be displayed. I know it can be done with bookmark and button but user is not convenient with that and they want slicer.

 

vinothkumar1990_0-1637121045744.png

 

Req2: User want to select the columns themself. For that they want an another slicer which give the provision to add or remove the columns from visual. If i take customer as example, they want 2 see two values in slicer i.e. Address and Phone. If they select Address then Address1, Address1 and Address3 columns should be added and if they select Phone then Phone1, Phone2 and Phone3 columns should added in one shot.

 

Can you please help.

 

Thanks,

Vinoth

 

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @vinothkumar1990 ,

 

I don't understand why the users say it's not convenient to have bookmarks and a button since I believe is easier to use just to click a button than select values from a slicer, but I have seen stranger requests.

 

In this case I see two solutions:

 

Unpivot and merge both your tables into a single one and the use it has your filters and slicing.

 

You can even use a single slicer with hierarchy has I have below.

MFelix_0-1637341078008.png

However taking into account the type of information you are presenting this table will be massive since I suppose that you have several orders. Also be aware that if you want then to drill trough or get data for calculaiton based on this it will imply to have measure calculated specifically and with manual filters.

 

I do not reccomend this approach but it's one possibility

 

Other option creater a table with the following setup:

MFelix_1-1637342413788.png

 

Add the following measures:

 

 

Customer Selection = IF(SELECTEDVALUE(Selection[Table]) = "Customer", 1)

Orders Selection = IF(SELECTEDVALUE(Selection[Table]) = "Orders", 1)

Selection = SWITCH(SELECTEDVALUE(Selection[Column]),
                "Product ID", SELECTEDVALUE(Orders[Product ID]),
                "Product Category ID",SELECTEDVALUE(Orders[Product Category ID]),
                "Customer",SELECTEDVALUE(Orders[Customer]),
                "Customer Name",SELECTEDVALUE(Orders[Customer Name]),
                "Customer Name",SELECTEDVALUE(Customer[Customer Name]),
                "Adress 1",SELECTEDVALUE(Customer[Adress 1]),
                "Address2",SELECTEDVALUE(Customer[Address2]),
                "Adress3",SELECTEDVALUE(Customer[Adress3]),
                "Phone1",SELECTEDVALUE(Customer[Phone1]),
                "Phone2",SELECTEDVALUE(Customer[Phone2]),
                "Phone3",SELECTEDVALUE(Customer[Phone3])
                )


 

 

Now setup a matrix for each of the value having:

Order ID and Customer ID for rows

SElection measure on values

Column on the Columns

 

Filter each one for not blank values of customer or orders depending on the data.

The play around with background and colour and you can show / hide the values at will.

 

 

Check PBIX file attach,


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
v-stephen-msft
Community Support
Community Support

Hi @vinothkumar1990 ,

 

I think @MFelix 's solution is quite good. Does his solution help you? If so, please mark his reply as the answer, and more people will benfit.

 

Best Regards,

Stephen Tao

MFelix
Super User
Super User

Hi @vinothkumar1990 ,

 

I don't understand why the users say it's not convenient to have bookmarks and a button since I believe is easier to use just to click a button than select values from a slicer, but I have seen stranger requests.

 

In this case I see two solutions:

 

Unpivot and merge both your tables into a single one and the use it has your filters and slicing.

 

You can even use a single slicer with hierarchy has I have below.

MFelix_0-1637341078008.png

However taking into account the type of information you are presenting this table will be massive since I suppose that you have several orders. Also be aware that if you want then to drill trough or get data for calculaiton based on this it will imply to have measure calculated specifically and with manual filters.

 

I do not reccomend this approach but it's one possibility

 

Other option creater a table with the following setup:

MFelix_1-1637342413788.png

 

Add the following measures:

 

 

Customer Selection = IF(SELECTEDVALUE(Selection[Table]) = "Customer", 1)

Orders Selection = IF(SELECTEDVALUE(Selection[Table]) = "Orders", 1)

Selection = SWITCH(SELECTEDVALUE(Selection[Column]),
                "Product ID", SELECTEDVALUE(Orders[Product ID]),
                "Product Category ID",SELECTEDVALUE(Orders[Product Category ID]),
                "Customer",SELECTEDVALUE(Orders[Customer]),
                "Customer Name",SELECTEDVALUE(Orders[Customer Name]),
                "Customer Name",SELECTEDVALUE(Customer[Customer Name]),
                "Adress 1",SELECTEDVALUE(Customer[Adress 1]),
                "Address2",SELECTEDVALUE(Customer[Address2]),
                "Adress3",SELECTEDVALUE(Customer[Adress3]),
                "Phone1",SELECTEDVALUE(Customer[Phone1]),
                "Phone2",SELECTEDVALUE(Customer[Phone2]),
                "Phone3",SELECTEDVALUE(Customer[Phone3])
                )


 

 

Now setup a matrix for each of the value having:

Order ID and Customer ID for rows

SElection measure on values

Column on the Columns

 

Filter each one for not blank values of customer or orders depending on the data.

The play around with background and colour and you can show / hide the values at will.

 

 

Check PBIX file attach,


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors