Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a dataset consisted a key column (K1), 10 attributes columns (A1,A2,...,A10) and four values columns (V1,V2,V3,V4). My ultimate goal is to create a 4-column dynamic table with fixed names (Key, Attribute One, Attribute Two, Value) by using field parameters or any other way. How can I achieve this goal?
K1 | A1 | A2 | A3 | A4 | A5 | A6 | A7 | A8 | A9 | A10 | V1 | V2 | V3 | V4 |
From this dataset, I want to create a dynamic table shown below to use in creating visuals (As an example A2,A4, V3 will be changed dynamically
to create a dynamic chart with a dynamic axis and legend.
Key | Attribute One | Attribute Two | Value |
K1 Info | A2 Info | A4 Info | V3 Info |
I appreciate a solution in PBIX file, if possible. Thank you
@Ritaf1983 @amitchandak @Ashish_Mathur would like to invite you for review of my problem. Thank you
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.
I will check it right now, thank you for your quick return.
User | Count |
---|---|
96 | |
85 | |
77 | |
66 | |
63 |
User | Count |
---|---|
110 | |
96 | |
96 | |
67 | |
59 |