Forum Discussion

erolyucel65's avatar
erolyucel65
Frequent Visitor
2 years ago

Create a dynamic chart from a dataset

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? 

 

K1A1A2A3A4A5A6A7A8A9A10V1V2V3V4
               

 

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

4 Replies

  • 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]
    )
    • erolyucel65's avatar
      erolyucel65
      Frequent Visitor

      I will check it right now, thank you for your quick return.

    • erolyucel65's avatar
      erolyucel65
      Frequent Visitor
      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.