How could implement selecting columns which shows in table dynamically?

[Anonymous]

Hi,

I have requirment as follow:

we have a table :

A1	A2	A3	M1	M2	M3

we need to give to user possibility to select A1, A2,A3 to show in the table or not.

I found this link :https://community.powerbi.com/t5/Desktop/Select-columns-to-show-on-table-visual/m-p/606363/highlight...

But in this link the attributes(A1,A2,A3 )were put rows, but I need them as column.

I appreciate any suggestion and help on this problem.

Thank you.

Regards,

Matin 

[Anonymous]

Thank you @AllisonKennedy  for the solution .

I follow the steps but the value of measure is always 0! and the solution does not work.
here are my tables :

and I created measure as follow:

ShowColumns =

Var SlicerSelection = VALUES(Slicer[Column1])

Var AlwaysShow = {("A1"); ("A2"); ("A3")}

Var ShowValues = UNION(SlicerSelection; AlwaysShow)

RETURN

IF(SELECTEDVALUE('Table'[Attribute]) IN ShowValues; 1; 0)

do you have any Idea?

I appreciate your help on this matter.

Regards,

Matin

[AllisonKennedy]

How have you configured the matrix visual? The measure will only work if there is only 1 Attribute selected, so you need to have attribute in Columns in the Matrix, and put this measure as the visual level filter as I explained in my previous reply, as a 'top N' filter. Can you share image of your report visual page Visualizations and Filters Pane to see what fields you've put in the visual? Redact any confidential info from the visual.

[Anonymous]

@AllisonKennedy 

here is my Matrix configuration and Filter :

[AllisonKennedy]

Make sure the Top N is set to Top 1. Sorry I should have specified in original post. I have updated now.

[Anonymous]

@AllisonKennedy , even with Top 1 does not work.

here is the result, as you could see the measure returns 1 for A1,A2,A3 ( which I want to be selacteble), and 0 for M1, M2 and M3.

and infact Slider does not doing anything .

for sure I did something wrong but I could not find it 😞

[AllisonKennedy]

Sorry, I did it the other way around and made M1, M2, M3 selectable. Just update the measure to replace A1, A2, A3 with M1, M2, M3:

ShowColumns =
Var SlicerSelection = VALUES(Slicer[Column1])
Var AlwaysShow = {("M1"); ("M2"); ("M3")}
Var ShowValues = UNION(SlicerSelection; AlwaysShow)
RETURN
IF(SELECTEDVALUE('Table'[Attribute]) IN ShowValues; 1; 0)

[Anonymous]

Thank @AllisonKennedy , now it works.

I really appreciate your help .

Regards,

Matsa

[AllisonKennedy]

Use a matrix instead of a table, and put the attributes in 'columns' field and turn 'show on rows' to ON. https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-august-2017-feature-summary/#valuesOnRows

[amitchandak]

@Anonymous , see if this can help

https://www.youtube.com/watch?v=6jeSIRpjv0M

for measure

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115

[Anonymous]

Thank you @amitchandak , it is very useful, but still there is a problem.

I need to make A1,A2,A3 selectable and always show M1 and M2 and M3 .
But when I added M1,M2 and M3 to Matrix as columns , they don't add to matrix.
do you have any suggestion to solve this problem?

sorry maybe my questions are so basic, because I am beginners in Power BI

Regards,

Matin 

[v-lionel-msft]

Hi @Anonymous ,

What problem have you met? You just need to do like this.

1. Unpivot the columns.

2. Add the columns to the matrix.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @v-lionel-msft  for the explanation,But this is not what I need.

I have a table like this:

C	A1	A2	A3	M1	M2	M3

and I need to show it as it is , but I need to put  a slider in order to give possibility to select or not select A1,A2,A3.

I did unpivot the columns A1,A2 and A3.

I put attribute as a slider and in Matrix:

C,M1,M2,M3 as ROWS

Attribute as columns

Values: first value 

But My result is like this:

C	M1	M2	M3	A1	A2	A3

and A1,A2 and A3 are selectable.

Do you have any suggestion in order to solve my problem?

Thank you in advance.

Regards.

Matin

[v-lionel-msft]

Hi @Anonymous ,

Try to unpivot A1, A2 and A3 columns, then, you can get such a table.

And add columns to the matrix.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @v-lionel-msft ,the steps which you mentioned is what I did( as I explained before), but the result is not similar to what I want, I need the result as follow:

C	A1	A2	A3	M1	M2	M3

Regards,

Matin 

[AllisonKennedy · Accepted solution]

@Anonymous  It is a bit unconventional but you can try to unpivot all the A and M columns, so that you are left with: 

C, Attribute, Value

Also create a disconnected table (no relationships to current data model) for the M values that you want user to select. I called this table Slicer.

Slicer:

Select

M1

M2

M3

Then create the matrix with C in Rows, Attribute in Columns, Value in Values.

Finally, create this measure to filter the Attribute: 

ShowColumns =

Var SlicerSelection = VALUES(Slicer[Select])

Var AlwaysShow = {("A1"), ("A2"), ("A3")}

Var ShowValues = UNION(SlicerSelection, AlwaysShow)

RETURN

IF(SELECTEDVALUE('Table'[Attribute]) IN ShowValues, 1, 0)

Put a filter on the visual for Attribute using Top N and use the ShowColumns measure as the By Value to filter for Top 1.