Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 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
    9 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

bsas's avatar
bsas
Icon for Post Patron rankPost Patron
9 years ago

Hi Anonymous,

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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.