Forum Discussion

Ski900's avatar
Ski900
Helper II
7 years ago
Solved

Help creating a dimension using two columns with blank values removed

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!


Need Help
  • Ski900's avatar
    Ski900
    7 years ago

    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

5 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    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

     

  • BobBI's avatar
    BobBI
    Resolver III
    7 years ago

    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's avatar
      Ski900
      Helper II
      7 years ago

      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
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    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.