Add multiple columns with one selection from slicer

[vinothkumar1990]

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.

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

[v-stephen-msft]

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 · Accepted solution]

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.

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:

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,