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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
abloor
Helper IV
Helper IV

Create a distinct list/table from multiple other tables

Hi,

 

I need to create a list/tabe of distinct/unique Names, but I need to take the values from a few different tables.

 

I have done this already in my document from one table with the below measure.  Can you please help me change it so I take it from 3 places? eg Table 1, Table 2 and Table 3?  These tables each have a slightly different list of names, some of which are duplicated in each table, some are unique.

 

NameBridging = CALCULATETABLE(DISTINCT('Table 1'[Name]),'Table 1'[Name] <> BLANK())

 

Thanks in advance!

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

Hi @abloor ,

 

To fix the error "Circular dependency detected" , you might not use CALCULATE or CALCULATETABLE function in the DAX bridge table.

 

You can create calculated table like DAX below, then you can filter out the blank value and create relationships as you need.

 

Table = UNION(DISTINCT('Table 1'[Name]), DISTINCT('Table 2'[Name]),DISTINCT('Table 3'[Name]))

78.png

Best Regards,

Amy

 

Community Support Team _ Amy

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

5 REPLIES 5
v-xicai
Community Support
Community Support

Hi @abloor ,

 

To fix the error "Circular dependency detected" , you might not use CALCULATE or CALCULATETABLE function in the DAX bridge table.

 

You can create calculated table like DAX below, then you can filter out the blank value and create relationships as you need.

 

Table = UNION(DISTINCT('Table 1'[Name]), DISTINCT('Table 2'[Name]),DISTINCT('Table 3'[Name]))

78.png

Best Regards,

Amy

 

Community Support Team _ Amy

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

Is it possible to use filter on one of the table to remove some unwanted attributes from the table 

Thanks @v-xicai that seems to have done the trick.

 

Thanks for you help @Anonymous as well.

Anonymous
Not applicable

@abloor please try values and union function

Table = UNION(
VALUES(Table1[Name]),VALUES(Table2[Name]),VALUES(Table3[Name]))

Thanks @Anonymous  that seems to be doing the trick however I can't link up the new table to Table 1 in the relationships view as it says there is a Circular dependency detected.  It's letting me link up Table 2 and 3 however.  I will have a play to see if I can get it working, but if you or anyone else has any other ideas that would be great.

 

Thanks very much.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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