Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I have two Direct Query tables with a Many-To-Many relationship (Table1 and Table2). They are connected by a key that is made up of a concatenated Transaction # and Order #.
I have a Table Visual that holds records of Transaction, Order, and some other data elements from Table2. How can I create a measure filter that filters this visual to only show records that have a concatenated key that also exists in Table1?
Solved! Go to Solution.
You could create a measure like
Value is in Table1 =
VAR CurrentKey =
SELECTEDVALUE ( 'Table2'[concatenated key] )
RETURN
IF ( CurrentKey IN ALL ( 'Table1'[concatenated key] ), 1 )
and then use that as a visual level filter to only show when the value is 1
You could create a measure like
Value is in Table1 =
VAR CurrentKey =
SELECTEDVALUE ( 'Table2'[concatenated key] )
RETURN
IF ( CurrentKey IN ALL ( 'Table1'[concatenated key] ), 1 )
and then use that as a visual level filter to only show when the value is 1
@johnt75 That worked perfectly!
Could you explain what IN and ALL are doing so I can understand how it works?
ALL returns all the values of Table 1 key regardless of any filters which may be applied, including any filters from the many-to-many relationship.
IN then checks to see whether the current key exists in those values.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
26 | |
10 | |
10 | |
9 | |
6 |