Forum Discussion

hello-pbi's avatar
hello-pbi
New Member
6 years ago

Get Values from table using userrelationship and filters

Hi,

 

I have two tables with an inactive many to many relationship and no active relationships. I'll call them table A and table B. Both tables have a different number of rows. I'm trying to bring a dates column from table A into a calculated column in table B. I'm doing a row level search on table A to find the match for the new calculated column in table B by using certain filter. 

 

My current function in table B now looks like this - 

 

= calculate(count(tableA[column 1]), Filter(tableA, tableA[date1] < tableB[date2] && .........&& tableB[date2] > tableA[date2]), USERELATIONSHIP(tableA[Apple], tableB[Apple]))

 

the above function works perfectly and returns the count. 

However, I need the value in tableA[Column 1] to be returned using the filter in the above function and only if the above function returns 1 and then blank if it returns anything but 1.

 

I am not sure how to modify the above written function to return the value which would be ideal while keeping all criteria of the above function. Also, if I am referencing the above function in any other column in tableB, then I get a circular dependency error or a variable error. 

 

Please help.