Introducing a third dimension based on two unrelated dimension tables?

[Vegard1985]

Hi,

My head might not be placed right at the moment, I'm struggling a to find a solution for this.

This should definitely not be a unique scenario, but I don't find information on it.

I have a model with sales data where I try to minimize data in the fact table to hopefully increase performance. The company has constructed a business area logic, that is basically derived from a combination of Customer master data and Product master data. This can of course be derived through if's and but's and creating a logic in the fact table itself, which has been the case up untill now. But I'm trying to "decouple" this business area logic, and wanted to see if it was doable/feasible to create this business area logic just based on the Customer and Product master data alone. But of course, these are two different tables, and are not related in any way.

Is it possible to create a relation between the customer and product dimension tables to arrive at a business area logic, which could be used to filter the sales data? Or am I just working in a dead end?

[amitchandak]

@Vegard1985 , a measure of fact create a relation between

Refer this  example

https://www.youtube.com/watch?v=cyOquvfhzNM

In the same way we can create new logic the dim work with each other

We can use https://docs.microsoft.com/en-us/dax/treatas-function

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

[Vegard1985]

Hi amitchandak,

Thanks - but I didn't quite understand.

Could you please guide me to the concept of how this could work for me?

Let me try to explain my scenario a bit more:

• We are two different groups with 10-20 legal companies in each, merged to one
• We have a groupwide main dimension to split business into several areas, and because of different legacy systems, this determination is a bit different for each group
• This dimension could be determined based on the country of sales (some areas are country specific, others are clusters, and you would also have a "the rest") and the customer group from customer master data, and the product group, a sub category, and a brand owner from the product master data
• Some transformations and additional information is needed to get this "mapping" in place
• We currently have this model running, but the performance is so-so, and I want to lean this process, and I was hoping that we could get this dimension away from the fact table because of obvious reasons.

What I was hoping to go for, was a third table where we have the mapping cleared out based on the "key" you can create in the customer master data and the "key" from product master data. But I struggle with trying to connect the dots from the customer and product master data, to this third dimension.

I end up with the "only one filtering path between tables is allowed".

This is from my last attempt, where I first thought to create a many-to-one relationship from the customer and product tables using a bridge, then likewise for the "business area determination" table. I understand this relationship is troublesome for Sales Data - but I was just trying.

The error pops up once I try to set Customer Bridge - Business Area Bridge relation direction to Both.

Do you mean there's some trick to work around this?