Forum Discussion
Join two tables without unique column (DAX)
You could try with a calculation like this. I first create a table returning the distinct values of def_id where experiment_id exists in Table1 and then count the rows of the new table.
Measure = VAR DefTable = CALCULATETABLE( VALUES( Table2[def_id] ); FILTER( ALL ( Table2[experiment_id] ); CONTAINS( VALUES( Table1[experiment_id] ); Table1[experiment_id]; Table2[experiment_id] ) ) ) RETURN COUNTROWS( DefTable )
If you instead need to could all rows in Table2 instead of the distinct values of def_id you will only have to make a small change to the code where instead of returning the distinct values of def_id you return the distinct rows of all the table instead. If you will have multiple rows in Table2 with the same value in all columns then you will have to add an Index Column to the table to make sure that all rows have unique values.
Measure = VAR DefTable = CALCULATETABLE( VALUES( Table2 ); FILTER( ALL ( Table2[experiment_id] ); CONTAINS( VALUES( Table1[experiment_id] ); Table1[experiment_id]; Table2[experiment_id] ) ) ) RETURN COUNTROWS( DefTable )
Depending on there you live you might have to replace the ';' in the code with ','
- Anonymous9 years agoNot applicable
sdjensen: This is what I tried
test = CALCULATETABLE(VALUES('table2'[def_id]),FILTER(ALL('table2'[experiment_id]),CONTAINS(VALUES('table1'[experiment_id]),'table1'[experiment_id],'table2'[experiment_id])),(COUNTROWS(table2')))
error: The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.
Whenever I try to enter 'var' and return in my measure I get syntax error
- sdjensen9 years agoSolution Sage
Please make sure that you type the formala exactly like mine - I tested it with demo data in a small model I created and it works like a charm. The VAR and RETURN is cusual for this to work.
You should still make sure to replace ; with , and of course replace table/column names to match your own data.
You can't test the CALCULATETABLE syntax with a measure. The code returns a table, so if you want to test it without the return you should select 'new table' instead, but you have to leave out the countrows part then.