dealing with blanks in DAX

[jfla]

I  have 2 tables (actuals and budgets) that each include a WBS#, but some of the budget rows can be blank. Actuals always have it. 

I'm trying to create a new table of all the unique WBS# across both tables. 

when all rows in these table have a WBS#, this DAX works:

WBS_DIM = DISTINCT (UNION(DISTINCT(ERP[WBS_ERP]), (DISTINCT(Table_Tasks[WBS])) ))

but when the Table_Tasks includes blanks, then I get an error.  I have tried the DAX below but

WBS_DIM = FILTER (
DISTINCT (
UNION(
DISTINCT(ERP[WBS_ERP]),
DISTINCT(Table_Tasks[WBS])
)
),
NOT ISBLANK (Table_Tasks[WBS])
)

I get an error that states "A single value for column 'WBS' in table 'Table_Tasks' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as"

I'm new to dax (but used to write SQL).  any help would be great!

thanks - 

j

[jfla · Accepted solution]

I tried this, and it  worked:

WBS_DIM = FILTER (

                DISTINCT (

                    UNION(

                        TOPN ( 0, ROW ("New_WBS", "asdf" ) ),

DISTINCT(erp[wbs_erp]),

                        DISTINCT(Table_Tasks[WBS])

                    )

                ),

            [New_WBS] <> BLANK ()

            )

[tamerj1]

Hi @jfla 

I think the problem is that the columns have different names. Try store them in variables as follows

or if youneed to remove all blanks use

 

[jfla]

Hi!  thanks.  I used the second option, but now it get 

"The ALLNOBLANKROW function expects a table reference for argument '1', but a table expression was used."

I checked my syntax multiple  times and it looks correct. 

[Whitewater100]

Hi:

I know tamerj1 has been working with you, so I hope not to step on toes. Anyways maybe:

DISTINCT(

       UNION(

         ALLNONBL:ANKROW(ERP[WBS_ERP]), 

         ALLNONBLANKROW(Table_Tasks[WBS])

)