How to unpivot specific columns of the table using DAX?

Question

Hey, so a novice here. I dont want the data to be leaked so I have hidden it and marked it with tags though.

Basically what you are looking at is the accuracy data of a classification model. As you can see, for some classes like "A" it predicts all data points correctly, thus it is flagged as True, but we dont have a False column for A because there are no False values. For some reason when we created this table in DAX by grouping a couple of columns and performing the count operation on different table,DAX didnt show False for any values that were completely True.

On the other hand, for "D", 185 values are true and 2 values are false.

We need to showcase this as like a bar chart with maybe a slicer for the different categories. For that we first need the "False" attribute for the values that predicted completely true, then we need to compute the percentage of False and True predictions for each class and then we need to show that in a bar chart.

We are thinking we should pivot the "FLAG" and the "Predicted Values" column for this, and then we will be able to do all the things aforementioned. But because this table was created using DAX from another table, we are unable to find a way to pivot it. It wont evens how up in the Power Query editor. Any tips or help are welcomed. Sorry again, Im a novice so just learning as I work..

Answer 1

Starting with : (Using enter data and copy paste from excel)

Adding the following code in the advanced editor under Transforme Data:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUQoJCnUFUkbmJsYWSrE60UpJCEFDAzOwUDJCyMTUFCyUgqTKAiHk5ugTDDYOLJKKpMjMBCyUhmy6BVwIps8QLJKOpMjUHCyUgSRkbAJXBdNnAnFoJpIQxFFZqI6KBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [category = _t, Flag = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"category", type text}, {"Flag", type logical}, {"Value", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Flag", type text}}, "en-SE"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Flag", type text}}, "en-SE")[Flag]), "Flag", "Value", List.Sum)
in
#"Pivoted Column"

Gives:

Which is what I assume you are after

Answer 2

You don't need to pivot. You need a flag dimension table joined to your existing table on flag. The dimension contains just two values for true and false and the cardinality is one-to-many unidirectional. Then on your bar chart, make sure to check show items with no data in the field well.