Fellow Power BI Users -

Wanted to get your eyes on this to see if i am doing this the correct way.
You normally say try and merge on as many lookup tables into the facts.

In this situation It has been really difficult to merge on pretty much nothing - as the Rows of Data is too big.
Am I doing the right thing here by creating a supporting table (top section)
1-2Many. (filtering both way) .
I found if the filtering is only single the “total sale” number is static in 2nd screen shot u see.

If you can please give me your views, advice on how to handle this level of complexity please.

Much appreciated - thanks in advance.

First these relationships look like they are all multi directional relationship with the double arrows. You want these to be one to many.

Well the nulls are a problem. If there’s no data, or no recorded information on a particular row then Power BI will aggregate those all up as blank.

I’m not sure there’s any way around this unless the actual data is fixed up.

Also I’m very confused why you think you need these tables

What are these actually doing versus just using the lookup table that actually exist?

I don’t really think these are required in this case. I just doubt you will need to filter lookup table together that can’t then filter the fact table. It just doesn’t seem logical to me.

The whole idea with using this technique is so that the filters in your model act like a waterfall. One level to the next.

In this case they don’t though because you don’t have any connection from the second layer down to the fact table.

Hi Sam to answers your questions.

Yes I know they are multi directional.
The reason being is my lookup tables still have many ID, Product ID or Transactional ID ,so i’ve used a supporting table to get my unique ID filtering into the fact. Example. Lets take Transaction History.

-I cannot link Trans History Tab into FactPurchaseOrder. Because theres no Transaction ID.
-My supporting table has unique product id, so I am linking this with the Transaction History - Product ID column
-The reason why the relationship is MD (Product ID to Transaction History) is because the the dax total sale wont work.
-Product ID supporting table into the FactPurchaseOrder is 1-2-M.

This is 1-2-Many here.

This is 1-2-Many here - (filter is on both now)

Based on the above relationship types.
The Product ID Lookup Supporting Table relationship is going into Fact with 1-To-Many (single filter)

do you see what I mean?
Hoping I am not confusing you here. Just wanted to know if this process/logic is correct on this advanced model. I would upload this file but it is big.

Thanks in advance.

I still just don’t see why you need these additional central lookup tables.

Can you think of any insight you need that benefits from this setup?

I honestly can’t right now.

As mentioned the idea around multi layered models is for a continuous flow of relationship down towards your fact table.

From what I can see this does not happen so to me there is no point in these tables. They do not assist in adding any value to calculations you might need to complete.

Also filtering both ways again seems pointless to me. What calculations is this supporting? I totally do not recommend this.

I feel you’re making your model way more complicated than it needs to be. Try to simplify it.

Every table that has many values as it’s index rather than the unique values should move down to your fact table layer. You should not have them as lookup tables with multi directional relationships.

To me this is not required.



This is my recommendation to you.

Hi Sam
Thanks for the response on this.