Forum Discussion
matrix show columns based on slicer selection
I have a table with multiple columns structure similar to this:
| measure 1 | measure 2 | measure 3 | measure 4 | |
| company a | ||||
| company b |
I am trying to ad a slicer and based on the slicer result, show different segment of the table. For example, if slicer select 1, show measures 1 and 2; if slicer select 2, show measure 3 and 4.
I was able to create a slicer, but stuck at grouping columns using the code below. Error message says it contains multiple columns, cannot be converted to scalar value.
DynamicTable =
VAR SelectedValue = SELECTEDVALUE(ParameterTable[Parameter], "None")
RETURN
SWITCH(
SelectedValue,
"Slicer value 1",
SELECTCOLUMNS(
'table',
"measure 1", [measure 1],
"measure 2", [measure 2]
),
"Slicer value 2",
SELECTCOLUMNS(
'table',
"measure 3", [measure 3],
"measure 4", [measure 4]
))
Hi whichOne
Based on your needs, I have created the following table.
Then you can create a measure by using the following dax:Dynamic = SWITCH(TRUE(), SELECTEDVALUE('Parameter (2)'[Parameter]) = 1,CALCULATE(SELECTEDVALUE('Table'[VALUE]),FILTER('Table','Table'[Column1] = 1 || 'Table'[Column1] = 2)), SELECTEDVALUE('Parameter (2)'[Parameter]) = 2,CALCULATE(SELECTEDVALUE('Table'[VALUE]),FILTER('Table','Table'[Column1] = 3 || 'Table'[Column1] = 4)), SELECTEDVALUE('Table'[VALUE]) )
Put the corresponding fields into the table so that you can make the table display specific columns based on the slicer selection.Result:
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- Greg_Deckler
Community Champion
whichOne Pretty certain that you need to use a disconnected table for your columns and then a single measure that determines what to display based on your 4 measures.
Could you elaborate a bit on how to do that?
- Greg_Deckler
whichOne Use an Enter Data query and enter data like:
Column1
Measure1
Measure2
Measure3
Measure4
Don't connect it to anything (no relationships). Use this in your Columns field well in your matrix and in your slicer. Then use a single measure along the lines of:
Selected Measure = VAR __Measure = MAX('Disconnected Table'[Column1]) VAR __Result = SWITCH( __Measure, "Measure1", [Measure1], "Measure2", [Measure2], "Measure3", [Measure2], "Measure4 ) RETURN __ResultAh ok I see what you mean. The column names might be confusing, they are not actually measures, they are just my table column attributions, I am calling them "measure". Should I make all the columns in the table measure first? I dont think I can just use columns directly like you purposed.
Hi whichOne
Based on your needs, I have created the following table.
Then you can create a measure by using the following dax:Dynamic = SWITCH(TRUE(), SELECTEDVALUE('Parameter (2)'[Parameter]) = 1,CALCULATE(SELECTEDVALUE('Table'[VALUE]),FILTER('Table','Table'[Column1] = 1 || 'Table'[Column1] = 2)), SELECTEDVALUE('Parameter (2)'[Parameter]) = 2,CALCULATE(SELECTEDVALUE('Table'[VALUE]),FILTER('Table','Table'[Column1] = 3 || 'Table'[Column1] = 4)), SELECTEDVALUE('Table'[VALUE]) )
Put the corresponding fields into the table so that you can make the table display specific columns based on the slicer selection.Result:
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
