Forum Discussion
Table that combines distinct values from different tables
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?
2 Replies
- lbendlin
Super User
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"})
)
- muhammad_786_1
Solution Supplier