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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kristel_tulio
Helper III
Helper III

Get the correct values when have multiple tables with indirect relationship

Hi, 

I would like to ask if there is a solution on how to get correct values if you have different tables and have indirect relationship.

Please see my data model below.

 

I do have 5 tables connected to each other and I would like to the values for my Builder Income and Builder COGS by Job Name but it's showing me the total instead not by Job name.

And also the totals need to be corrected. Thank you

 

Job have relationship with Job Register and Job Budget through "Job UID"

Job Register have relationship with Accounts through"Account UID"

Accounts have relationship with Journal Transactions through"Account UID"

Journal Transaction have relationship with Dates through "Date".

 

Calculation:

Builder Income = CALCULATE( IF( SUM( 'Journal Transactions'[Sales Calculation] ) = 0, BLANK(), SUM('Journal Transactions'[Sales Calculation] ) ) )

 

Builder COGS = CALCULATE( IF( SUM( 'Journal Transactions'[Cost of Sales Calculation] ) = 0, BLANK(), SUM( 'Journal Transactions'[Cost of Sales Calculation] ) ) )

 

kristel_tulio_0-1677025420491.png

 

4 REPLIES 4
amitchandak
Super User
Super User

@kristel_tulio , is the relation following 1-M  where

Job -> Job Register (1-M)

Job Register -> Account (1-m)

Account - >Journal Transactions (1-m)

 

Then I think it should work. If that is not true, have a relook at the schema design.

 

Prefer a star Schema

 

Hi @amitchandak 

Yes on this

Job -> Job Register (1-M)

Job Register -> Account (M-1)

Account - >Journal Transactions (1-m)

@kristel_tulio , Job Register to Account has an issue

 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Hi @amitchandak

I think you're right but I'm not sure, what is wrong and how to fix it. I tried it by pulling the Job Name from Job Register, Classification from Accounts and Amount from Journal Transactions

kristel_tulio_1-1677048385715.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors