1 fact table or 2 tables to see in which month orders come in and in which month they get invoiced?

[Chris_Cools]

Hello,

i'm new to Power BI coming from Qlikview where the datamodel was built so many years ago by our software provider with all facts in 1 large table, handling facts by different facttypes.

From what i read and find online Power BI works better with multiple fact-tables for each process.

i'm testing now and came up with this:

Now i can't get my head around what to do if i want to calculate what part of certain orders is allready invoiced.

For example i want to know for all orders that came in month X, what part is allready invoiced and also in what month has that been invoiced.

Or for example the same logic but not compared against time, compared against Customer:

What part of customers orders has allready been invoiced.

What do i do in need to do with the model to make this possible?

Do i need to introduce in the invoice table the order date for the order where the invoiceline was based on?

Do i need to put them in 1 table instead of seperate ones?

I have a sample file here:

sample file 

Thanx a lot! all aid or pointers are appreciated!!

Chris

[Chris_Cools]

Hello, 

Does anyone has a good tip for an article or book with some practical use cases of worked out models to get some ideas?

thanx!

Chris

[aj1973]

Hi @Chris_Cools 

Your Model looks ok ! what makes you think that it needs adjustments?

DAX can help you navigate between tables and get the calculations you need.

[Chris_Cools]

Hello Amine Jerbi,

Thank you for confirming!

it feels a little strange to me because all of these years i was told that the model that was made for us needed to have 1 big fact table to work with and for Power Bi i hear/read the opposite.

So now i am a  bit lost if i need to figure out for example when orders between 2 dates get invoiced or what part of the orders of customer X has allready been invoiced.

And i was wondering if that would require an extra table linking the ordernumber in the FactInvoice to the FactOrders or if that would have to be solved with DAX, but reading your answer i presume that it is in DAX?

kind regards,

Chris

[aj1973]

looking closer to your tables, the FactInvoice doesn't have the Order Id or Order Number. Something is missing !?

[Chris_Cools]

These are the two fields in each table containing the ordernumber:

grtz

Chris

[aj1973 · Accepted solution]

You can create an Inactive Many to Many relationship and then use USERELATIONSHIP function in a DAX measure. https://www.youtube.com/@SQLBI/search?query=userelationship

Or you can add a bridge table between the 2 Facts, the table should contain a none duplicate Order number coming from the FactOrders.

https://www.youtube.com/@SQLBI/search?query=bridge%20table

[Chris_Cools]

Thank you very much for these tips!! I will look into these options!

Kind regards,

chris

[aj1973]

Welcome