Is this the way to go (relate tables, show visuals over 2 tables)

[ErwinJanssen]

In Power BI Desktop I would like to have 2 different tables that work together.

We (must) use DirectQuery, and i have both tables in my Power BI dekstop.

Table 1 contains 12 rows with a budget per month.

Table 2 contains spend costs in a month.

In SQL this looks like this:

In Power BI i have created a new column in each table with the value UniqueColumnForRelation = Budget[CountryCode] + Budget[YearInt] + Budget[MonthInt] and the other way around in the other table.
After that, I have created the relation. That works (i think).

Now I want to show in a visual the budget for month 5 (1 value, set by a filter with months) and below the spend costs for that month (summed values for 1 month).

What I have now, is that it also sums also budget (for the whole year). I think my relation is not correct? If so, how should I related these tables?

Thanks in advance.

[Eric_Zhang]

---

@ErwinJanssen wrote:

In Power BI Desktop I would like to have 2 different tables that work together.

We (must) use DirectQuery, and i have both tables in my Power BI dekstop.

Table 1 contains 12 rows with a budget per month.

Table 2 contains spend costs in a month.

In SQL this looks like this:

In Power BI i have created a new column in each table with the value UniqueColumnForRelation = Budget[CountryCode] + Budget[YearInt] + Budget[MonthInt] and the other way around in the other table.
After that, I have created the relation. That works (i think).

Now I want to show in a visual the budget for month 5 (1 value, set by a filter with months) and below the spend costs for that month (summed values for 1 month).

What I have now, is that it also sums also budget (for the whole year). I think my relation is not correct? If so, how should I related these tables?

Thanks in advance.

---

@ErwinJanssen

As long as those two tables are in a one to many relationship, it shall work. How you do set filter for months, is it a Slicer, how are the years and Country codes filtered? For further suggestion, would you mind uploading the pbix file(change it to import mode before uploading)? You can upload it to Onedrive/Google drive and share a link.

[ErwinJanssen]

Hi Eric, thanks for your reply. They are in a one to many relationship.

I created a pbix file, with the situation as I have it, including the data:

https://stack.rwinjanssen.nl/s/Kz3iCOhrYjXNOFl

It probably is something simple/stupid.

Thanks in advance 🙂

[ErwinJanssen]

Anybody? The pbix is in previous post.
Thanks in advance.

[mow700]

Perhaps you could created a calculated table that contains just the unique keys from both tables, and then change the relationship to use it as an intermediary.  For example, first create the lookup table:

LookupTable = DISTINCT(UNION(DISTINCT('Blad1'[Column]),DISTINCT('Blad1 (2)'[Column])))

Then change the relationships to something like this:

Now you should be able to calculate and display results from both tables as expected.

[ErwinJanssen]

Thanks, this indeed works, but only when you have all data, and not with DirectQuery. A new table can only be created when you are not using DirectQuery.

[TomMartens]

Hey,

the problem you are facing, is due to the fact that you are using the year and month column from your Blad1 table, this does not filter down your table Blad2

More general speeking, you have two different granularities in your 2 fact table, you should have a look at these two sides:

• http://www.daxpatterns.com/handling-different-granularities/
• http://www.daxpatterns.com/budget-patterns/

Start with the first one, and then the 2nd.

Hopefully this gets you started

Regards

Tom

[ErwinJanssen]

yes, the first link seems to demonstrate what I need indeed. But, i have to be fresh to dive into it 😛

I'll let you know when I was able to fix it like this, thanks!