Forum Discussion

sree2604's avatar
sree2604
Regular Visitor
1 year ago
Solved

Need Help with Data Model / DAX

I am working on making a dataset more user friendly / light in size. Data Model along with DAX look very promising and would greatly appreciate any help to achieve the below.     Problem:  I need t...
  • sanalytics's avatar
    sanalytics
    1 year ago

    sree2604 
    Please create proper data model rather than only create DAX.. beacuse DAX works on relationship.. Your two tables have different grain so you should have two fact table.. Below is the complete screenshot of your data model

    You need to have a simple star schema

     

    Complete pbix file is attached below.

     

    Hope it helps

    Regards

    sanalytics

     

     

     

     

v-menakakota's avatar
v-menakakota
Community Support
1 year ago

Hi sree2604 ,

Thank you for reaching out to us on the Microsoft Fabric Community Forum.


Thank you sanalytics  for providing possible solution,that mentioned  separating the data model into two fact tables with shared dimension tables really make a difference. Since the Sales and Lives data are at different grains, trying to force them into one table (like I initially did in Power Query) was increasing the model size unnecessarily.

After restructuring the model (with a proper star schema), calculate Sales per  Million Lives using clean DAX without needing to duplicate rows.

Total Sales := SUM(FactSales[Sales])
Total Lives := SUM(FactLives[Lives (in M)])

Sales per Million Lives :=
DIVIDE([Total Sales], [Total Lives] * 1000000, 0)

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.