Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 ...
  • AlbertoFerrari's avatar
    AlbertoFerrari
    8 years ago

    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

AlbertoFerrari's avatar
AlbertoFerrari
Most Valuable Professional
8 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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] )
    )
)

     

    • AlbertoFerrari's avatar
      AlbertoFerrari
      Most Valuable Professional
      8 years ago

      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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thank you Alberto ;)

  • iamafanijaz's avatar
    iamafanijaz
    Frequent Visitor
    3 years ago

    The Solution works for me whilw using Summarize with union 🙂