Help creating a dimension using two columns with blank values removed

[Ski900]

As the title says I am trying to create a dimension/attribute table using distinct, nonblank values from two columns, each from separate tables. For example, using the sample data below, Dimension[Assigned To] is the desired result based on Table A and Table B. 

Table_A[Assiged To]         Table_B[Assiged To]      Dimension[Assinged To]
John                                  Eric                               John
Anne                                 Anne                             Anne
Bill                                    (blank)                          Eric
Bill                                    Bill                                Bill  
Emily                                Ryan                             Ryan
                                                                             Emily

Here is what I have tried so far. I feel I'm close but I'm missing something small, I could be wrong though. 

Dimension = 
var A = SUMMARIZECOLUMNS(
    Table_A[Assigned To],
    FILTER(Table_A, ISBLANK(Table_A[Assigned To]) = FALSE)
)
var B = SUMMARIZECOLUMNS(
    Table_B[Assigned To],
    FILTER(Table_B, ISBLANK(Table_B[Assigned To]) = FALSE)
)
var A_Column = SELECTCOLUMNS(A, "Assigned To", Table_A[Assigned To])
var B_Column = SELECTCOLUMNS(B, "Assigned To", Table_B[Assigned To])
return
NATURALINNERJOIN(A_Column, B_Column)

I get the error "An incompatible join column, [Assigned To] was detected. 'NATURALINNERJOIN' doesn't support joins by using columns with different data types or lineage". The data types are the same, I get what lineage mean, but my brain is firing blanks on how to resolve it. Any help is much appreciated!

[Ashish_Mathur]

Hi,

Ensure that both columns have the same heading.  Using the Query Editor, simply append both Tables.  Right click on the column and under Transform Data, select Upper case, Trim and clean.  Right click on the column and click on Remove Duplicates.  In the Filter drop down, unchek the Blanks checkbox.

[BobBI]

Hi,

if you have TableA and TableB containing same values as in the example above. Here below is the DAX and desired result.

Hope this helps

EVALUATE

Var A = SELECTCOLUMNS( 
                        ALLNOBLANKROW(TableA),
                        "Name",TableA[Name])
        
VAr B = SELECTCOLUMNS( 
                        ALLNOBLANKROW(TableB),
                        "Name",TableB[Name])
        

Var C = DISTINCT(UNION(A,B))
return
C

Result

John

Anne

Bill

Eric

blank

Ryan

you can further use AllNonBlankRow(C) to remove Blank value.

Cheers

Bob

[Ski900 · Accepted solution]

Thanks for the reply! I was not able to use ALLNOBLANKROW(c) because the function was expecting a table, and it was treating c as a column. However, adjusting your code slightly I came up with this that works

Dimension = 
var a = SELECTCOLUMNS(FILTER(Table_A, ISBLANK(Table_A[Assigned To]) = FALSE), "Assigned To", Table_A[Assigned To])
var b = SELECTCOLUMNS(FILTER(Table_B, ISBLANK(Table_B[Assigned To]) = FALSE), "Assigned To", Table_B[Assigned To])
var c = DISTINCT(UNION(a,b))

return c

[BobBI]

Great, glad it works.

[v-yuta-msft]

Hi Ski900 ,

Could you show some screenshots of raw data in table A, B and C? In addtion, maybe the issue comes from that blank value still exist in your filtered table, so modify dax like pattern below and check if it can work:

A =
SUMMARIZECOLUMNS ( Table_A[Assigned To], IGNORE ( Table_A[Assigned To] ) )

B =
SUMMARIZECOLUMNS ( Table_B[Assigned To], IGNORE ( Table_B[Assigned To] ) )

Regards,

Jimmy Tao