Forum Discussion
Join two tables without unique column (DAX)
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().
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?
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: 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