Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I'm facing some problems when trying to count the amounts yes, no and partly for all the questions in one.
My goal is to visualize the amounts of yes, no and partly through a bar/pie chart en be able to filter it with the first three columns.
So in the example below, the total amount of yes is 12, no 14 and partly 5 should be showed through the bar/pie chart.
Country | Region | Type | Question 1 | Question 2 | Question 3 | Question 4 | Question 5 | Question 6 |
The Netherlands | North | Supermarket | Yes | Yes | No | |||
The Netherlands | West | Cinema | No | Yes | Partly | Yes | No | |
The Netherlands | East | Cinema | Yes | No | No | Partly | ||
The Netherlands | South | Supermarket | No | No | ||||
Belgium | North | Supermarket | partly | No | No | No | ||
Belgium | West | Cinema | Yes | No | Yes | Yes | Yes | |
Belgium | East | Cinema | No | No | Yes | Partly | Yes | |
Belgium | South | Supermarket | Partly | Yes | No |
Thanks in advance!!
Solved! Go to Solution.
Hi @Brndn ,
Select 6 question columns and click unpivot feature in Query Editor and you will get a new table.
Then create visuals as below.
Best Regards,
Jay
Hi @Brndn ,
Select 6 question columns and click unpivot feature in Query Editor and you will get a new table.
Then create visuals as below.
Best Regards,
Jay
Thank you so much! Exactly what I was looking for
Try this solution.
1. Unpivot the Question columns in Power Query:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"dZHPCsMgDMZfRTz3KTZ2lUEHY5QehIVZprX457C3b6u2apcejET8fsmXdB19CCAMnAAj+fi2tKFMGyeWu/UTGMXNF9ySvcDukRSHado3GOYJdtVdhxEUjx83/Z0bJ38JEJ9OMTdeY0jVRYCGkJg4pNUesRR0pCTFZGVcQH4Gr07HscmmzUruJFnJhOMkdselmxxrMeY/1zrM81+OO0cWUO6znwE=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [
Country = _t,
Region = _t,
Type = _t,
#"Question 1" = _t,
#"Question 2" = _t,
#"Question 3" = _t,
#"Question 4" = _t,
#"Question 5" = _t,
#"Question 6" = _t
]
),
ChangeType = Table.TransformColumnTypes(
Source,
{
{"Country", type text},
{"Region", type text},
{"Type", type text},
{"Question 1", type text},
{"Question 2", type text},
{"Question 3", type text},
{"Question 4", type text},
{"Question 5", type text},
{"Question 6", type text}
}
),
UnpivotColumns = Table.UnpivotOtherColumns(
ChangeType,
{"Country", "Region", "Type"},
"Attribute",
"Value"
),
RenameColumns = Table.RenameColumns(UnpivotColumns, {{"Attribute", "Question"}}),
FilterBlanks = Table.SelectRows(RenameColumns, each ([Value] <> " "))
in
FilterBlanks
2. Create measure:
Count Value = COUNT ( Questions[Value] )
Proud to be a Super User!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
83 | |
64 | |
60 | |
55 |
User | Count |
---|---|
179 | |
108 | |
105 | |
71 | |
70 |