Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello,
I need to reproduce the following Excel charts in my Power BI, the excel can be found here : https://we.tl/t-wkNmNiaQGL ) :
Here is the pbix file : https://we.tl/t-yS7mPD7HzJ
If you cannot access the link, here is the M code :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WysxLzkxJzSspVtJRMjQAEpZADKKNlWJ1opVSUnMT81JSQbKmIBUgwhzMAcmW5Jck5sCEjUygchZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SerieName = _t, LessThan1 = _t, Between1And3 = _t, Between3And5 = _t, GreaterThan5 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SerieName", type text}, {"LessThan1", Int64.Type}, {"Between1And3", Int64.Type}, {"Between3And5", Int64.Type}, {"GreaterThan5", Int64.Type}})
in
#"Changed Type"
I don't know if it is possible or if I must have three different queries, one for each line of the actual table ?
I saw the "Line and stack column chart" visual that would seem to be OK, but I can't figure out how to set it.
Do you have any idea please ?
If there is a need to remodel the data, it's not a problem.
Solved! Go to Solution.
I came back with a solution with 1 table, like this I don't have the issue of the relationships anymore. What I had to do was just to transpose my data. But I then had an issue with the ordering of my X-axis. I first added an "order" column, but if a one column has more than similar value, it cannot have 2 different order number. In harder to not hardcode anything in the values, I added spaces in front of the Y-axis elements and it works fine. I didn't test but it seems I could have a generic way by adding a dax column like this :
SortSchool =
RANKX(
'Table',
CALCULATE(MIN('Table'[Grade]), ALLEXCEPT('Table', 'Table'[school])), , ASC, Dense
)
You can find my solution here (tab : Page2) in a V3 : https://we.tl/t-jWEKY451bI
I have tried the idea I had with 3 queries (I have extracted each line and unpivoted them) and it works (almost) perfectly. Here is the V2 of the pbix : https://we.tl/t-c2iHv1YpIo
I get that :
I have 1 issue left, it automatically creates the following relationships :
I have tried to remove them because I don't think they are relevant, but then I don't get the correct charts anymore... :
So is there a way to remove those relationships and still have my charts displayed as expected ?
Thanks for your help.
I came back with a solution with 1 table, like this I don't have the issue of the relationships anymore. What I had to do was just to transpose my data. But I then had an issue with the ordering of my X-axis. I first added an "order" column, but if a one column has more than similar value, it cannot have 2 different order number. In harder to not hardcode anything in the values, I added spaces in front of the Y-axis elements and it works fine. I didn't test but it seems I could have a generic way by adding a dax column like this :
SortSchool =
RANKX(
'Table',
CALCULATE(MIN('Table'[Grade]), ALLEXCEPT('Table', 'Table'[school])), , ASC, Dense
)
You can find my solution here (tab : Page2) in a V3 : https://we.tl/t-jWEKY451bI
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
85 | |
82 | |
69 | |
49 |
User | Count |
---|---|
143 | |
123 | |
107 | |
61 | |
55 |