2 Fact tables filtering problems

jmcph · ‎10-11-2020

Hi. I am having issues with slicing thru dates and the results of my filter.

I have 2 fact tables, Order and Accounting, they are connected with a unique identifier (PO #). My model looks like this:

My table has PO #, Amount (From Acctg) and Amount (from Order) and it works as i intended. Screenshot below:

However, when i add a filter from SupplierList table. It shows weird results as follows:

It seems like it shows all my supplier name disregarding the PO #.

How to remedy this? I hope you can help me with this. Thank you!

Sorry for the messy tables, i havent cleaned them yet.

CNENFRNL · ‎10-11-2020

Hi, @jmcph , it's all about a very fundamental mechanism of filter propagation in Power BI.

Essentially filter propagates automatically along a chain of One-to-many (1:*) relationships among tables; so natually your table with 'PO KEY'[P.O. #], Accounting[Amount] and Order[Amount] works well as intended.

In the meantime, such a filter propagation doesn't automatically occur inversely, say from Many side to One side along a Many-to-one relationship. That's why you get a wrong answer while slicing SupplierList. We can achieve a uphill (from Many to One) filtering with a bit more skill as descibed in detail in this article.

https://powerpivotpro.com/2014/08/filters-can-flow-up-hill-via-formulas-that-is/

Another versatile solution to such an "uphill filtering" senario is Expanded Table. We can even easily filter down Acct Calendar table from SupplierList without appending extra relationships or without authoring complex filtering measures.

If you attach a dummy file, it would be easier to illustrate some more details to you.

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

VijayP · ‎10-11-2020

@jmcph This is because there is not direct relation ship between two tables. (Supplier and Accounting) you need to establish that relation ship to achieve. Do you have any field with which supplier Table can be connected?

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!

jmcph · ‎10-11-2020

I see. So i need to put up a direct relationship. I can link it via Suppliercode, but it will be encoded in the Accounting data.

The thing is, all these data are being manually inputed using excel. I dont want to put extra burden on the encoders of Accounting data to input the Suppliercode. Is there a way that these indirect relationship might work?

Thank you for your response! Greatly appreciate it!