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[As...
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
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
  • BobBI's avatar
    BobBI
    Resolver III
    7 years ago
    Great, glad it works.