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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Use ALLNOBLANKROW with UNION

I have made a table joining two columns using UNION taking only DISTINCT values.

That works fine, but I also need the blanks to be removed.

I have tried using ALLNOBLANKROW, but can't seem to make it work.

 

Is there any way to get rid of the blanks using DAX?

Casenumbertable = 
DISTINCT (
    UNION (
        SELECTCOLUMNS ( testcare; "casenumber"; testcare[CaseNumber] );
        SELECTCOLUMNS ( tvs; "casenumber"; tvs[CaseNumber] )
    )
    ) 
1 ACCEPTED SOLUTION

ALLNOBLANKROW serves a different purpose, it removes the optional blank row to fix invalid relationships and it has nothing to do with blank removal (I know, the name is somewhat confusing... but it would have been hard to find a better one).

 

That said, you can remove blanks after the UNION is finished:

 

Casenumbertable = 
DISTINCT (
    FILTER ( 
        UNION (
            SELECTCOLUMNS ( testcare; "casenumber"; testcare[CaseNumber] );
            SELECTCOLUMNS ( tvs; "casenumber"; tvs[CaseNumber] )
        ),
        NOT ISBLANK ( [CaseNumber] )
    )
) 

This should do the trick.

 

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

Alberto Ferrari - SQLBI

View solution in original post

7 REPLIES 7

ALLNOBLANKROW serves a different purpose, it removes the optional blank row to fix invalid relationships and it has nothing to do with blank removal (I know, the name is somewhat confusing... but it would have been hard to find a better one).

 

That said, you can remove blanks after the UNION is finished:

 

Casenumbertable = 
DISTINCT (
    FILTER ( 
        UNION (
            SELECTCOLUMNS ( testcare; "casenumber"; testcare[CaseNumber] );
            SELECTCOLUMNS ( tvs; "casenumber"; tvs[CaseNumber] )
        ),
        NOT ISBLANK ( [CaseNumber] )
    )
) 

This should do the trick.

 

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

Alberto Ferrari - SQLBI

The Solution works for me whilw using Summarize with union 🙂

Anonymous
Not applicable

Thank you Alberto 😉

Anonymous
Not applicable

That does the trick Alberto!

A small correction is needed though, since I'm using ";" and not "," for making breaks.

I guess this is due to different local version quirks of Power BI.

Below is my European version of the code:

Casenumbertable = 
DISTINCT (
    FILTER ( 
        UNION (
            SELECTCOLUMNS ( testcare; "casenumber"; testcare[CaseNumber] );
            SELECTCOLUMNS ( tvs; "casenumber"; tvs[CaseNumber] )
        );
        NOT ISBLANK ( [CaseNumber] )
    )
) 

 

Yeah... I find this stuff of "," and ";" so annoying that I ended up setting all of my PCs with American standard, at least I can write code the way I like, with commas as separators.

 

After some time, you also get used to the funny way they express dates "mm/dd/yyyy". It still does not make a lot of sense, yet you get used to it 🙂


Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

 

Alberto Ferrari - SQLBI
bsas
Post Patron
Post Patron

Hi @Anonymous,

 

As a variant you can filter blank rows by report/visual filter.

Anonymous
Not applicable

Not what I'm looking for, but thanks for the effort.

The table will serve as a relation(primary key) between two tables between two other tables, so the blanks need to be gone.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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