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
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.