cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vegard1985
Helper I
Helper I

Introducing a third dimension based on two unrelated dimension tables?

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?

 

2 REPLIES 2
amitchandak
Super User
Super User

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

 



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

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.

onlyoneallowed.png

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.