Filter on 2 Dim tables with OR condition

[Alpesh]

Hi

I have a fact table example below, but has another 60 columns and row count >2 million

Case ID	Shop ID	Product ID	Channel ID
abc126862	10111	560008373572	7676823

These then have Dim tables, which have a hierarchy and has a relationship on the ID (One to Many)

e.g., Product Dim table (the other tables are the same in that they have a hierarchy) - has >50K records

Product L1	Product L2	Product L3	Product L4	Product L5	Product ID
					560008373572

Shop Table -has > 900 records

Shop L1	Shop L2	Shop L3	Shop ID
			10111

I need to be able to filter for an OR condition  e.g., Product L3 = "ABC" OR Shop L1 = "XYZ". How can i do this? I attempted to create a measure using crossjoin and filter however, when published the measure returns no results, so I assume this isn't the right solution. I could write an if statement in Power query, however, there are over 100 Product IDs as part of Product L3 = "ABC" so the formula would be massive and not very easy to adjust every time we've got a new product

[Alpesh]

Brilliant,  had an error: function RELATED expects a fully qualified column reference as its argument, but then added the measure on the Fact table (instead of my measures table) and worked perfectly. Thank you!

[mlsx4 · Accepted solution]

Hi @Alpesh 

If tables are well connected, you should be able to do something similar to this using RELATED to take fields from other tables:

FILTER('FactTable',
		RELATED(Products[ProductID]) = "ABC" || 
		RELATED(Shops[ShopID]) = "XYZ")
	)