Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
I am hoping to get some help on the data model design of a dashboard I am building. I think it should be fairly straight forward, but I want to be sure I am doing this right.
Currently I have just three tables in a simple star schema layout:
Now, I want to create some new fields based on the monthly data in the Billing_Metrics table. There are more but this should give an idea of what I am looking for. I would like to track these at both the client and location level:
Ok, so the question is where the best place for these new fields in the data model would be and how should they be joined to the current structure? I can’t work it out in my head how that would look, especially when it needs to be at both the client and location level. Can anyone point me in the right direction?
Thank you!!!
Solved! Go to Solution.
Hi @Anonymous
For your three points requirements, since i don't know the real situation, i would give some examples and introduce some availabe methods you can learn.
Measures – Month to Month Percent Change
Comparison- current month vs previous month
To calculate "Flags that would indicate things such as a client (or location) that had charges in the previous month, but has zero for the current month", you could consider "IF" and "COUNT" function, ect.
Additionally,instead of putting client and location fields in the slicer, if you want to show the values in teh same table at both the client and location level, you could use ALLEXCEPT in the measures above.
If you have any problem to implement this, please show me details.
Best Regards
Maggie
Hi @Anonymous
For your three points requirements, since i don't know the real situation, i would give some examples and introduce some availabe methods you can learn.
Measures – Month to Month Percent Change
Comparison- current month vs previous month
To calculate "Flags that would indicate things such as a client (or location) that had charges in the previous month, but has zero for the current month", you could consider "IF" and "COUNT" function, ect.
Additionally,instead of putting client and location fields in the slicer, if you want to show the values in teh same table at both the client and location level, you could use ALLEXCEPT in the measures above.
If you have any problem to implement this, please show me details.
Best Regards
Maggie
Thank you for your reply, @v-juanli-msft ! I will work on incorporating this advice into my project.
Are there any performance issues with DAX measures when scaling up? Someone on my team suggested this type of thing should be calculated within SQL Server and then fed into PowerBI in a view. Is there any real argument for that?
Hi @Anonymous
You could create relationships among these tables like
create measures in the Metrics table,
for example, Three month averages for units sold
sum_3_months = CALCULATE ( SUMX ( ALLSELECTED ( billing_metrics ), billing_metrics[units_sold] ), DATESINPERIOD ( billing_metrics[date], LASTDATE ( billing_metrics[date] ), -3, MONTH ) ) average_3_months = [sum_3_months]/3
Best Regards
Maggie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |