Data Model Design Question

[Anonymous]

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:

• Client table
• Location table (Clients have multiple locations)
• Metrics table for monthly billing

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:

• Three, six, and twelve month averages for units sold and revenue.
• Percentage change from previous month to current month for units sold and revenue.
• 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.

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!!!

[v-juanli-msft · Accepted 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.

12 months averages,

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

[Anonymous]

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?

[v-juanli-msft]

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