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
czaldumbide
Helper I
Helper I

Fixing table relationships

Hello Everyone,

 

The current relationships among my tables are not alloiwng me to derive a table visualization with my desired output. Let me walk you through my tables and what I've got so far so you can understand what I want to achieve.

 

I have 4 tables:

1) Accounts Table

Account IDUser ID
1A
2A
3B

4

C

5

D

6

D

 

2) User Table 

User ID
A
B
C
D
E

 

3) Risk Accounts Table

Account ID
1
3
5

 

4) Transactions Table

Account IDAmount
1$50
1$100
2$50
3$40
5$70
6$10

 

The Accounts and User Table are related (many to 1) using User ID as the key. The Accounts and Risk Accounts table are related (1 to many) using Account ID as the key. And my Transactions and Accounts table are related (Many to 1) using Account ID as the key. 

 

Using these relationships I get the following table visualization:

User IDAccount IDSum of Amount
A1$150
B3$40
D5$70

 

But this is not what I want to see. Basically if a client appears in the Risk Accounts table, I would like to see the transactions made by all of the accounts of that client (even those that do no appear in the Risk Accounts table). So in the following table I also get transactions for Account ID 2 and 6 becuase they are related to User ID A and D. The table would look like this:

User IDAccount IDSum of Amount
A1$150
A2$50
B3$40
D5$70
D6$10

 

Let me know if you understood what I'm trying to achieve. Any help would be appreciated. 

Thanks

1 ACCEPTED SOLUTION
v-lionel-msft
Community Support
Community Support

Hi @czaldumbide ,

 

Try to refer to the .pbix file.

k2.PNG

.pbix 

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

6 REPLIES 6
v-lionel-msft
Community Support
Community Support

Hi @czaldumbide ,

 

Try to refer to the .pbix file.

k2.PNG

.pbix 

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

v-lionel-msft
Community Support
Community Support

Hi @czaldumbide ,

 

Like this? Maybe you can try again.

h8.PNGh9.PNG

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

@v-lionel-msft ,

 

In the snapshot you provided I don't see how you use Risk Accounts to filter the table. If you could provide a little more information on how you did it I would appreciate it. 

 

Thanks!

 

Hi @czaldumbide ,

 

Sorry for the late reply. The reason why I don’t use the User table and the Risk Accounts table is because I think they are meaningless.

.PBIX  

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-lionel-msft ,

 

Thanks for your response. I provided a bad example of my transactions table. If you use the following instead, then you will see why you need to include the Risk Accounts Table. You need a way to filter out Account ID #4 from your final output.

 

4) Transactions Table

Account IDAmount
1$50
1$100
2$50
3$40
4$100
5$70
6$10

 

Let me know if you can figure it out now. 

Hi @v-lionel-msft ,

 

Thanks for the suggestion. I currently do have that relationship between Accounts and Transactions Table, but I saw you did get the right end product. Could you please attach the pbix file so I can take a look at how you did it?

 

 

 

 

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.