Cross filtering of data in same column and same table

[AishwariyaV]

Hi

I am currently working on a dataset, which is similiar to the one below

Raw material number	Type
a	category 1
b	category 2
c	Region 1
d	Region 2
e	Region 3
e	Category 1

With this, i am showing two bar charts.

Chart 1 has category as axis and value as count of raw material(which is 3).

Chart 2 has region as axis and value as count of raw material(which is 3).

These charts are filtered as separate visuals using filter pane.

A raw material can have both category and region types.

When a particular category is selected in chart 1, region has to be cross filtered in chart 2 and vice versa.

How can we achieve this??

[Icey · Accepted solution]

Hi @AishwariyaV ,

Try this:

1. Transform data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUXJOLElNzy+qVDBUitWJVkpCFjICCyUDhYJS0zPz86BqUhACEBWpCAFjuACywbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Raw material number" = _t, Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Raw material number", type text}, {"Type", type text}}),
    CategoryTable = Table.SelectRows(#"Changed Type", each Text.Contains([Type],"Category" )),
    RegionTable = Table.SelectRows(#"Changed Type", each Text.Contains([Type],"Region" )),
    MergeTable = Table.NestedJoin(CategoryTable, {"Raw material number"}, RegionTable, {"Raw material number"}, "RegionTable", JoinKind.FullOuter),
    #"Expanded RegionTable" = Table.ExpandTableColumn(MergeTable, "RegionTable", {"Raw material number", "Type"}, {"Raw material number.1", "Type.1"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded RegionTable", "Custom", each if [Raw material number] = null then [Raw material number.1] else [Raw material number]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Raw material number", "Raw material number.1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Raw material number"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"Raw material number", "Type", "Type.1"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns",{{"Type", "Category Type"}, {"Type.1", "Region Type"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns2",{{"Raw material number", Order.Ascending}})
in
    #"Sorted Rows"

2. Create visuals:

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[amitchandak]

@AishwariyaV , If the table shows source data. I think it would be a good idea to unpivot the same and try

https://radacad.com/pivot-and-unpivot-with-power-bi

[AishwariyaV]

Thanks for the reply @amitchandak .

But my source data itself is in unpivoted format only.

The problem is ,since i am using filter pane to filter out category and region in two separate visual, when i select "Category 1" bar of chart 1, instead of showing "Region 3" in chart 2, it is showing blank.