How can I create a filter to filer 2 tables with no connection

Viewed 35

In my PowerBI model, I have 2 tables,

Table1

category1LevelCount category2LevelCount month
3 7 Sept
8 9 Oct
13 11 Nov

Table 2

category1Min category2Min month
1000 1023 Sept
1003 1110 Oct
1034 11231 Nov

In my Power BI, I have 2 clustered column chart2, 1 for each table and the x-axis is month and y is the values (e.g. category1LevelCount , category2LevelCount in 1 chart, whereas category1Min, category2Min).

My question is these 2 tables has no relationship, but I want to create a filter so that when I set the filter to 'category1',

  • chart 1 will show only category1LevelCount
  • chat 2 will show only category1Min

(i.e. only show y-axis with 'category1' in all charts)

Can you please tell me if that is possible?

1 Answers

For this use-case you need to unpivot your data, since your tables do not have the category designation as rows in a column. Your Table 1 must look like this:

month Attribute Value Category
Sept category1LevelCount 3 Category 1
Sept category2LevelCount 7 Category 2
Oct category1LevelCount 8 Category 1
Oct category2LevelCount 9 Category 2
Nov category1LevelCount 13 Category 1
Nov category2LevelCount 11 Category 2

And your Table 2 must look like this:

month Attribute Value Category
Sept category1Min 1000 Category 1
Sept category2Min 1023 Category 2
Oct category1Min 1003 Category 1
Oct category2Min 1110 Category 2
Nov category1Min 1034 Category 1
Nov category2Min 11231 Category 2

You can paste this code into a blank query to see how to do this simple transformation on your minimal data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlbSUTIH4uDUghKlWJ1oJQsgxxKI/ZMhfEOQCkNDIOGXX6YUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [category1LevelCount = _t, category2LevelCount = _t, month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"category1LevelCount", Int64.Type}, {"category2LevelCount", Int64.Type}, {"month", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"month"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Category", each if Text.StartsWith([Attribute], "category1") then "Category 1" else "Category 2")
in
    #"Added Custom"

After loading these tables, you need to create a simple table that contains the Category selection. You can do this in many ways, but the simplest is probably to click "Enter data" in the Home -> Data tab, and create a 1-column table like this:

enter image description here

We will use this small table to push the Category filter to your two fact tables. When the tables are in the data model, connect them using relationships. In the Model view, it should look like this:

enter image description here

The additional small table will act as a filter on both Table 1 and Table 2 simultaneously, achieving the effect you asked for.

When you get to this point, you can create charts based on the month, Attribute and Value columns from Table 1 to create your first chart. The settings for a clustered bar chart are shown below, this will create one bar per month, with height according to the value column, and colors/attribute in legend according to the Attribute column:

enter image description here

From Table 2 to create your second chart. Use your Categories[Category] column as a slicer, and set the slicer to single-select if you want to force a single-category selection:

enter image description here

Related