Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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:
Thanx a lot! all aid or pointers are appreciated!!
Chris
Solved! Go to 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
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
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
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.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
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
looking closer to your tables, the FactInvoice doesn't have the Order Id or Order Number. Something is missing !?
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
These are the two fields in each table containing the ordernumber:
grtz
Chris
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
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Thank you very much for these tips!! I will look into these options!
Kind regards,
chris
Welcome
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook