Use 2 columns from different tables as a slicer

[shiggs8]

Hi, I have a very large transaction table and a couple of dimention tables linking to this. In my report, I need a slicer which filters the transaction line based on product info but also based on discount info. This is how my tables are set up currently but I need the flag from the transaction table in the same slicer as the flags from the product table.

I have also created a transaction flag table with the discount flag (and other transaction flags) in but I can't seem to get these in the same table with both relationships as active. 

Note: The dim tables are unpivoted so many-many relationships.

This maybe a long winded way of doing this so any suggestions will be great.

Thanks!

[Anonymous · Accepted solution]

Hi @shiggs8,

I'd like to suggest you create a new table to extract and union the flag from two tables and use it as the bridge to link these tables. After these steps, you can use this table as a filter to filter both two table records.

How to Join Many to Many with a Bridge Table in Power BI | Seer Interactive

Notice: power bi data model(AS tabular) does not allow multiple active major relationships, so you can consider using star schema to link different tables.

Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

Relationships in analysis services tabular models 

Regards,

Xiaoxin Sheng

[Greg_Deckler]

@shiggs8 Can you share example data? I think you will need a relationship between your dimension tables to have them in the same slicer as a hierarchy so maybe a bridge table?

[shiggs8]

Sure. I've tried linking the tables with another table but it won't allow an active relationship.

Transaction Table example:

Product Key	Trans Flag Key	Date	Balance	Quantity	Discount Flag
1234	123	07-Sep-21	20.00	1	Full Discount
1235	456	07-Sep-21	30.00	1	Full Discount
1236	678	07-Sep-21	0.00	1	Full Discount
1237	321	07-Sep-21	0.00	1	Full Discount
1238	321	08-Sep-21	0.00	1	N

Product Table example:

Product Key	Product Number	description	Flag
1234	123456	Example 1	Product Group 1
1235	123457	Example 2	Product Group 1
1236	123458	Example 3	Product Group 4
1237	123459	Example 4	Product Group 1
1238	123460	Example 5	Product Group 2

So I want the flag and the discount flag columns in the same slicer.

Here is what I tried:

Thanks