Forum Discussion
Create a dynamic chart from a dataset
First Unpivot data in Power Query by selecting all the attribute columns (A1 to A10) you want to unpivot.
Choose to "Unpivot Columns" to transform your columns into two columns one for the attribute names (Attribute) and one for the attribute values (Value).
Then create slicers for attribute and value selection.
- Add slicers to your report page by selecting the "Slicer" visualization.
- Use one slicer for selecting attributes (A1, A2, ...), based on the AttributeName column you created.
- Add slicers for each value column (V1, V2, V3, V4).
Now comes the DAX part for your dynamic behaviour :
Create a measure that uses the selected slicer values to determine which attribute and value to display :
Dynamic Value =
VAR SelectedAttribute = SELECTEDVALUE(Slicer[AttributeName])
VAR SelectedValue = SELECTEDVALUE(Slicer[ValueColumn], "V1") // Default to V1 if no selection
RETURN
CALCULATE(
MAX(Table[AttributeValue]),
Table[AttributeName] = SelectedAttribute,
Table[SelectedValue]
)Then create a table that compiles the selected key, attributes, and values into a structure that can be easily displayed:
Dynamic Table =
ADDCOLUMNS(
VALUES(Table[K1]),
"Key", Table[K1],
"Attribute One", [Dynamic Attribute One],
"Attribute Two", [Dynamic Attribute Two],
"Value", [Dynamic Value]
)Dynamic Value =
VAR SelectedAttribute = SELECTEDVALUE(Slicer[AttributeName])
VAR SelectedValue = SELECTEDVALUE(Slicer[ValueColumn], "V1") // Default to V1 if no selection
RETURN
CALCULATE(
MAX(Table[AttributeValue]),
Table[AttributeName] = SelectedAttribute,
Table[SelectedValue]
)
I could not get selected values from the slicers.It gives me error saying It could not find the AtrributeName and ValueColumn.