Forum Discussion

jmcph's avatar
jmcph
Helper III
5 years ago

2 Fact tables filtering problems

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. 

 

Need Help

4 Replies

  • VijayP's avatar
    VijayP
    Community Champion
    5 years ago

    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?

    • jmcph's avatar
      jmcph
      Helper III
      5 years ago

      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! 

      • VijayP's avatar
        VijayP
        Community Champion
        5 years ago

        jmcph 

        You can use TREATEAS Function to bring two different elements together

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    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.