Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I have the following SharePoint lists:
Plans:
Plan | Curve | Description |
Plan001 | Curve001 | |
Plan002 | Curve002 |
Curves:
Curve | ValueA | MeasureA | ValueB | MeasureB |
Curve001 | 10 | 100 | 50 | 100 |
Curve002 | 20 | 30 | 40 | 80 |
A User can select a Plan. When it gets selected, I want to draw a lina chart of the corresponding curve, where the datapoints consist of Value (X-Axis) and Measure (Y-Axis).
I managed to get all the values with either measure column or calculation column. But I am not able to draw the chart. I think it is because each field in curve is also a column.
The chart works perfectly when I create a table like this from hand:
Value | Measure |
10 | 100 |
50 | 100 |
Is there any way to create such a table automatically when a Plan gets selected? Or do you know any other way how I could achieve this?
Solved! Go to Solution.
Here is one possible approach
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tKks1MDBU0lEyNAATINIUxozVgSsxAgoZgcSNQYQJiLAAKogFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Curve = _t, ValueA = _t, MeasureA = _t, ValueB = _t, MeasureB = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Curve", type text}, {"ValueA", Int64.Type}, {"MeasureA", Int64.Type}, {"ValueB", Int64.Type}, {"MeasureB", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "List", each {{[ValueA],[MeasureA]},{[ValueB],[MeasureB]}}),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Curve", "List"}),
#"Expanded List" = Table.ExpandListColumn(#"Removed Other Columns", "List"),
#"Extracted Values" = Table.TransformColumns(#"Expanded List", {"List", each Text.Combine(List.Transform(_, Text.From), "#(tab)"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "List", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"x", "y"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"x", Int64.Type}, {"y", Int64.Type}})
in
#"Changed Type1"
Hi @crizbe ,
I think you can create a relationship between Plan table and Curve table by [Curve] column.
For reference: Create and manage relationships in Power BI Desktop
Then you can create a slicer by [Plan] column from Plan table.
By default:
Select Plan001:
Select Plan002:
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Yes, usually you would need to unpivot your Curves table - in this case in pairs.
What's the importance of "A" and "B" - does that play any role later?
Thanks, yes A and B are the X and Y Values of each point in the chart
Hi @crizbe ,
I agree with lbendlin's reply. Due to we cannot see calculated column or measure created by Dax in Power Query Editor, firstly, you need to calcualte MeasureA and MeasureB by M code in Power Query Editor. Then you can follow lbendlin's step to unpivot your table.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Here is one possible approach
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tKks1MDBU0lEyNAATINIUxozVgSsxAgoZgcSNQYQJiLAAKogFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Curve = _t, ValueA = _t, MeasureA = _t, ValueB = _t, MeasureB = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Curve", type text}, {"ValueA", Int64.Type}, {"MeasureA", Int64.Type}, {"ValueB", Int64.Type}, {"MeasureB", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "List", each {{[ValueA],[MeasureA]},{[ValueB],[MeasureB]}}),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Curve", "List"}),
#"Expanded List" = Table.ExpandListColumn(#"Removed Other Columns", "List"),
#"Extracted Values" = Table.TransformColumns(#"Expanded List", {"List", each Text.Combine(List.Transform(_, Text.From), "#(tab)"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "List", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"x", "y"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"x", Int64.Type}, {"y", Int64.Type}})
in
#"Changed Type1"
Thank you very much. It worked like a charm! I have one slight issue now. How am I able to filter the values now based on selectedValues?
Hi @crizbe ,
I think you can create a relationship between Plan table and Curve table by [Curve] column.
For reference: Create and manage relationships in Power BI Desktop
Then you can create a slicer by [Plan] column from Plan table.
By default:
Select Plan001:
Select Plan002:
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much! It works as expected now.
User | Count |
---|---|
3 | |
3 | |
2 | |
2 | |
2 |
User | Count |
---|---|
10 | |
4 | |
4 | |
3 | |
3 |