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