Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
shiggs8
Frequent Visitor

Use 2 columns from different tables as a slicer

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.

shiggs8_0-1631017718572.png

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

Thanks!

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Greg_Deckler
Super User
Super User

@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?


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

Transaction Table example:

Product KeyTrans Flag KeyDateBalanceQuantityDiscount Flag
123412307-Sep-2120.001Full Discount
123545607-Sep-2130.001Full Discount
123667807-Sep-210.001Full Discount
123732107-Sep-210.001Full Discount
123832108-Sep-210.001N

 

Product Table example:

Product KeyProduct NumberdescriptionFlag
1234123456Example 1Product Group 1
1235123457Example 2Product Group 1
1236123458Example 3Product Group 4
1237123459Example 4Product Group 1
1238123460Example 5Product Group 2

 

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

Here is what I tried:

shiggs8_0-1631025565437.png

Thanks

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.