Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |