Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi, I want to create a combo visual and so the data needed is one common axis column and two values,
I could't find a way to create visual with the original data, the data seems need to be unpivoted and there are many others fiters to be considered, like location and other columns not listed.
I am new user of DAX and ask for help, could this problem can be solved use DAX? Thank you.
Solved! Go to Solution.
Hi @Anonymous ,
Copy and paste this M-code into a blank query to get the details of each step.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYmMgNkeiTZVidaKVnKAiRmARBG0OlnUG6UXWbARXDpJ2AbLMkDQZI5THxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, bar_month1 = _t, bar_month2 = _t, bar_month3 = _t, line_month1 = _t, line_month2 = _t, line_month3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"bar_month1", Int64.Type}, {"bar_month2", Int64.Type}, {"bar_month3", Int64.Type}, {"line_month1", Int64.Type}, {"line_month2", Int64.Type}, {"line_month3", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Location"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.2", "Month"}})
in
#"Renamed Columns"
This will give you the expected result
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂
Hi @Anonymous ,
Copy and paste this M-code into a blank query to get the details of each step.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYmMgNkeiTZVidaKVnKAiRmARBG0OlnUG6UXWbARXDpJ2AbLMkDQZI5THxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, bar_month1 = _t, bar_month2 = _t, bar_month3 = _t, line_month1 = _t, line_month2 = _t, line_month3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"bar_month1", Int64.Type}, {"bar_month2", Int64.Type}, {"bar_month3", Int64.Type}, {"line_month1", Int64.Type}, {"line_month2", Int64.Type}, {"line_month3", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Location"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.2", "Month"}})
in
#"Renamed Columns"
This will give you the expected result
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂
Thank you very much, I will try it, and by the way, if I use DAX create a calculation that use switch, could it be possible to solve that?
Hi @Anonymous ,
You're welcome. It is slightly more complicated in DAX but it can be done. Take a look at these articles :
Unpivot using DAX
https://simplifypowerbi.com/?p=325
Pivot using DAX
https://dataap.org/blog/2021/06/25/pivot-your-table-in-dax/
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!
Kind regards,
Rohit
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
13 | |
12 | |
11 | |
11 |
User | Count |
---|---|
19 | |
14 | |
14 | |
11 | |
9 |