Forum Discussion
Modeling - consolidated tables
Hello dear members
I would like a confirmation regarding my logic behind a data model.
I have one fact table with data from a hospital with all the illnesses and the average cost of them. So for example illness: asthma, Cost : 1000$.
And I have another fact table with treatments. For example illness: asthma, treatment1: inhaler, cost: 100, treatement2: pills, cost: 50.
One illness could be linked to multiple treatments. Is it ok to consolidate the 2 tables together?
The problem here I think is that the average cost per Illness will be duplicate multiple times based on the number of treatments.
What do you think??
From modeling and power bi perspective is this ok to do??
Thank you very much
Hi Dimitris_Kats
For better data model, please create a DIM table for Illness and connect with both Fact table
You can enter data for DIM table directly in Power BI desktop or Power Query editor
Or usa DAX = DISTINCT(Table2[illnesscolumn]) to create the DIM table
Let me know if that works for you
If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.
Thanks
Pijush
Linkedin
2 Replies
Hi Dimitris_Kats
For better data model, please create a DIM table for Illness and connect with both Fact table
You can enter data for DIM table directly in Power BI desktop or Power Query editor
Or usa DAX = DISTINCT(Table2[illnesscolumn]) to create the DIM table
Let me know if that works for you
If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.
Thanks
Pijush
LinkedinPijushRoy thank you very much for replying to me. I totally agree with you
