Table that combines distinct values ​​from different tables

[nok]

Hello!

I have three tables in this structure:

Table1

Level	Value
AAA	12
BBB	34

Table2

Level	Category	Value
CCC	nbf	98
CCC	rtr	77
DDD	ght	56

Table3

Level	Type
EEE	Omn
FFF	Inky
FFF	Non

I want to create a single reference table that will serve as a filter in my dashboard and will be responsible for filtering all tables (table1, table2 and table3). This new table will have all the distinct values ​​of the Level column of each table, just like this:

Level

AAA

BBB

CCC

DDD

EEE

FFF

I created a new table in this format using this code:

UNION(VALUES(Table1[Level]), VALUES(Table2[Level]), VALUES(Table3[Level]))

However, when I try to relate this table to the tables from which it originates (table1, table2 and table3), I receive a circular dependency error.

How can I create a dynamic table that always has all the distinct values ​​of the Level column of each table so that I can relate to the tables and use it as a filter on my dashboard page?

[muhammad_786_1 · Accepted solution]

Hi @nok 

Just try this DAX formula, and it should solve the issue.

Best Regards,
Muhammad Yousaf

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

LinkedIn

[lbendlin]

You are on the right track.  Whenever you get a circular reference in DAX you can just say "Pah!"  and implement this in Power Query.

= Table.Distinct(

Table.SelectColumns(Table1,{"Level"}) &

Table.SelectColumns(Table2,{"Level"}) &

Table.SelectColumns(Table3,{"Level"})

)