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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

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 relationships. 

 

I've used this before and it's worked fine.. 

 

Can anyone help with this?

 

Thank you!

 

 

 

 Capture15.PNG

1 ACCEPTED SOLUTION
AlbertoFerrari
Most Valuable Professional
Most Valuable Professional

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

Alberto Ferrari - SQLBI

View solution in original post

2 REPLIES 2
AlbertoFerrari
Most Valuable Professional
Most Valuable Professional

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

Alberto Ferrari - SQLBI

When using NOT ISBLANK for only one column wrapped inside the DISTINCT() it's clear. But when aggregating 2 columns wrapped in a UNION() I didn't understand why the engine just gives me the first table[column] as a option to wrap inside the filter function. 

 

For example, when I use the NOT ISBLANK("I only can add the T1[C] option)

 

 

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors