Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Null/Blank value when making a distinct union between tables

Hi,    I'm trying to create a relationship between elements in different tables with the distinct union DAX, but it's returning a null/blank result in the new table, which then won't let me create ...
Need Help
Tips and Tricks
  • AlbertoFerrari's avatar
    AlbertoFerrari
    8 years ago

    First, use DISTINCT instead of VALUES, so to avoid the blank row which might be created due to invalid relationships. Then, if BLANK is present somwhere among teh values, you can simply surround the whole code with a FILTER that removes the blank:

     

    FILTER ( 
    DISTINCT ( 
        UNION ( 
             DISTINCT ( T1[C] ),
             DISTINCT ( T2[C] )
        )
     ),
     NOT ISBLANK ( [C] )
)


    Have fun with DAX!

    Alberto Ferrari
    http://www.sqlbi.com

AlbertoFerrari's avatar
AlbertoFerrari
Icon for Most Valuable Professional rankMost Valuable Professional
8 years ago

First, use DISTINCT instead of VALUES, so to avoid the blank row which might be created due to invalid relationships. Then, if BLANK is present somwhere among teh values, you can simply surround the whole code with a FILTER that removes the blank:

 

FILTER ( 
    DISTINCT ( 
        UNION ( 
             DISTINCT ( T1[C] ),
             DISTINCT ( T2[C] )
        )
     ),
     NOT ISBLANK ( [C] )
)


Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com