Forum Discussion

nok's avatar
nok
Icon for Advocate II rankAdvocate II
2 years ago
Solved

Table that combines distinct values ​​from different tables

Hello!

I have three tables in this structure:

Table1

LevelValue
AAA    12
BBB34

 

Table2

LevelCategoryValue
CCC      nbf            98
CCCrtr77
DDDght56

 

Table3

LevelType
EEE       Omn    
FFFInky
FFFNon

 

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?

  • 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

2 Replies

  • 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's avatar
    muhammad_786_1
    Icon for Solution Supplier rankSolution Supplier

    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