Filtering lookup table using fact table

Power BI Data Model
1

Hello Fellow Power BI Users -

I am from a manufacturing company. We make automotive parts and sell to customers as per their drawing. Every customer has set of part numbers and price for respective part numbers.
My data model looks like this

image
image.jpg698×381 71.4 KB

Following is report for one customer

image
image.png698×395 110 KB

I have the following questions

  1. Where should I have the ‘Part Number & Price’ table (AOPSBPPrice) - As Lookup table or fact table?
    In my data model, I have kept the same table in both look up and fact table

The reason why I have kept the table both as look up and fact table is –
To filter the list of part number and price that we supply to one particular customer
I create a table with Part Number from PartNumberCustomerMaster table and Price from AOPSBPPrice table
I filter the customer using page level filter in the report

But I get stuck when I have to compare price between two different customers in the same market segment (for example - Passenger Car)
The table in the right hand corner of the report is created without any measure but using a page level filter for particular customer.

Thanks in advance for your help

Krishna

2

Nice model setup, great work.

Always quite difficult to understand every nuance within a data model but my initial feel is that it needs to be a fact table as it seems like it logically would be on the many side of a relationship.

If I’m understanding it correctly there are customers, and parts referenced many times and these can and should be filtered by lookup tables with unique column of customers and parts also.

To compare customers you should be able to filter on customers from the lookup table, that should flow down your relationships and hit both the sales and pricing tables at the same time. Within you measures is where the harders work can be done. Usually with iterating functions.

Check out this video here for ideas around how to use iterating measure to assist with what you may need. I’m not 100% sure this is exactly what you need but I’m confident this will give you some ideas as it’s a similar data model setup.