Forum Discussion
Getting the detail for a summary view
Hi there,
Given the dataset
I have a grid view that displays a summary of Material with count of Product. I'm only interested in seeing materials where the count of Product is less than 3. In the visual, I have a filter on the visual that does this for me.
So far, so good...
Now I need to know which products make up the count of less than 3. The expected output would be:
Any ideas about how I would do this? I was thinking maybe a custom column but can't get my head around how to get this...
Thanks in advance
Adrian
Use a measure as a visual filter
Hi ATMAN777 - Please check the solution provided by lbendlin ,
we can achieve the same at power query editor part. please check the below code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tUNJRcs9JLC5WitWB8QMSC1KLkPlA+ZLMZLBIeGZeSn45iian/PxsFF1A9SWpCJFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Material = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Material", type text}}),
#"Grouped Rows" = Table.Group(Source, {"Material"}, {{"ProductCount", each Table.RowCount(_), type number}}),
#"FilteredTable" = Table.SelectRows(#"Grouped Rows", each [ProductCount] < 3),
#"MergedTable" = Table.NestedJoin(Source, "Material", FilteredTable, "Material", "FilteredData", JoinKind.Inner),
#"Expanded FilteredData" = Table.ExpandTableColumn(MergedTable, "FilteredData", {"Material"}, {"Material.1"})
in
#"Expanded FilteredData"Hope it works
2 Replies
- lbendlin
Super User
Use a measure as a visual filter
- rajendraongole1
Hi ATMAN777 - Please check the solution provided by lbendlin ,
we can achieve the same at power query editor part. please check the below code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tUNJRcs9JLC5WitWB8QMSC1KLkPlA+ZLMZLBIeGZeSn45iian/PxsFF1A9SWpCJFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Material = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Material", type text}}),
#"Grouped Rows" = Table.Group(Source, {"Material"}, {{"ProductCount", each Table.RowCount(_), type number}}),
#"FilteredTable" = Table.SelectRows(#"Grouped Rows", each [ProductCount] < 3),
#"MergedTable" = Table.NestedJoin(Source, "Material", FilteredTable, "Material", "FilteredData", JoinKind.Inner),
#"Expanded FilteredData" = Table.ExpandTableColumn(MergedTable, "FilteredData", {"Material"}, {"Material.1"})
in
#"Expanded FilteredData"Hope it works