Join two tables without unique column (DAX)

[Anonymous]

Hi,

I have two tables 

table 1

abc_id    experiment_id    

1                   10

4                   50

6                   12

3                   3

table 2

def_id  experiment_id

4                  3

7                 12

8                 11

5                  3

Now I want to return count of "def_id" where t1.experiment_id=t2.experiment_id. In above case it should return "3". There are no unique columns in both tables.

Please help.

[sdjensen]

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

[Anonymous]

@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

[sdjensen]

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.

[malagari]

You can create a calculated column in the first table with something like this:

= CALCULATE( COUNTROWS(Table2), FILTER(Table2, Table2[experiment_id] = Table1[experiment_id]) )

That will count all rows in Table2, where the current row's experiment_id equals the experiment_id in Table2.  If you want to sum the def_id, then change COUNTROWS() to SUM().

[Anonymous]

@malagari : It wont accept table 1 in the filter.  

error: A single value for column 'experiment_id' in table 'table1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

any idea?

[Anonymous]

Could you create a 3rd table that is a list of just the Experiments?  You could potentially build this table using the Edit Queries and doing an append of a few tables where you only keep the Experiment columns.  Now you link your Experiment table to both tables you have described. 

From here, its just a matter of making a measure to count each table and add the result together.

[Anonymous]

@Anonymous: Can you please explain it in little more detail. I understood the part about creating a new table with just experiment_id?  What to do next? 

P.S: I am new to this

[Anonymous]

In the table relationships section, link the Dimension table you just created, with the 2 other tables.  Now, whenever you want to do something by Equipment ID, you use the Dimension table field instead.  Power will string everything together through this table.