Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ErwinJanssen
Regular Visitor

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

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:

example-query.jpg

 

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?

wrong-chart.jpg

 

 

Thanks in advance.

7 REPLIES 7
Eric_Zhang
Employee
Employee


@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:

example-query.jpg

 

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?

wrong-chart.jpg

 

 

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.

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 🙂

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

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:

snip_20171010160126.png

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

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.

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:

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

 

Hopefully this gets you started

 

Regards

Tom

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.