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