Modeling - consolidated tables

[Dimitris_Kats]

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 

[Dimitris_Kats]

@PijushRoy thank you very much for replying to me. I totally agree with you 

[PijushRoy · Accepted solution]

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