Introducing a new measure table

Power BI Data Model
1

Hi,
As a new power bi user im looking for some clarification on best practice approach on how to process the introduction of a new table im working with. The new table is summarised at a higher level than the new measures table im looking to introduce. so i cant see how a straight relationship will work.

The data sources
I already have an existing table which has a month, country and each country has a further 29 subcategories, plus a measures column.
the challenge is that i have a new table with month and country only (no subcategories) and its associated measure.
So im looking for advice on the best approach to introduce the new two-tier table to the three-tier table.

cheers
peter

2

When you say measure column you mean calculated column? What is actually in the column?

You should really attempt to have a date table and a country table.

You will likely also need a country/categories lookup table also.

Have you been through the advanced modeling course yet?

This tutorial here actually explains this and should give you the idea you need to make this work

3

Hi Sam,
ok so im making good progress with your courses, really helping me get my head around data models etc.
i have a related but slightly different question now.
i have the following tables;

  1. target table - this is an annual target based on an expected country (level 3 mean meaning highest country grouping) rate figure (its not a sum figure, so i cant break out in the way you mention in your videos)
  2. two supporting tables - monthly data
  3. As you can see i created a country level 3 table to give me a distinct row of countries, as you advise.
    my issue is i cant work out how to show the unique target figure for each country along side the associated data from the two other monthly based tables without it summing up all the months
    Cheers
    peter
    data%20model
    data model.PNG1523×723 47.3 KB
4

Ok so the model is incorrect for what you need.

This is very similar to the budget techniques that I showcase a lot. The target table needs to be a fact table not a lookup as you have it.

Think your filters need to flow down to your calculations so they can filter at the same time. ie. your actual results vs your targets results.

You have a one to many down to your lookup table, which is not going to help at all.

See this image for an example (this is from the completed model for the advanced modeling course)

image
image1201×662 35.8 KB

To showcase how this is done I’ve created many videos. Here are a number of links to these. They all revolve around budgeting techniques.

Also the advanced modeling course also goes through setting this up correctly.

If you can understand these videos, you will understand how to fix this very easily.

Sam

5

Hi Sam,
I had actually watched these all already. But as always i missed one small part which was important to get the model working.
so now i need to remove the blank rows, i know ive seen you mention it somewhere (DAX command at the end to hide rows) but cant find it.
cheers
peter

remove%20rows
remove rows.PNG521×628 5.51 KB

6

You can use IF( ISBLANK( …), BLANK(), …)

This logic should do it.

But usually if there is no results it shouldn’t show up anyway, so I’m not sure why it’s not doing this already.

It’s hard to tell without being able to see more.