Forum Discussion
Helper ICan't join tables, how retrieve value from another table?
Because, for some rows in Journal there are more than one row with matching IDs, you have to handle multiple possibilities. For example, in your case, it is possible to have one or more matching IDs, so you could create a measure like the following to get the displayed resultss:
Here is text for the calculated column that you can copy for the above measure:
NUM from Procurement =
VAR vRowsWithMatchingIDsInProcurment =
SUMMARIZE ( FILTER ( Procurement, Procurement[ID] = Journal[ID] ), [NUM] )
RETURN
SWITCH (
COUNTROWS ( vRowsWithMatchingIDsInProcurment ),
0, BLANK (),
1, LOOKUPVALUE ( Procurement[NUM], Procurement[ID], Journal[ID] ),
CONCATENATEX ( vRowsWithMatchingIDsInProcurment, [NUM], ", " )
)Remember that this is only one way to handle the different scenarios (for example, maybe instead of concatenating, you want to get the maximum or minimum (top or bottom) NUM.
Tom
Helper IHi Tom,
I've tried the solution you offered. But it doesn't work. This is de code i've tried:
Imported Measure (new name in table A) =
CALCULATE (
[Measure in table B],
INTERSECT (
ALL ( 'XX'[project & invoice code] ),
VALUES ( 'XX'[projcect & invoice code] )
)
)
When I try this, i'll get an error "Too many arguments were passed to the COUNTROWS function. The maximum argument count for the function is 1. :
TEST MEASURE TABLE A =
CALCULATE (
COUNTROWS('Table B'[Measure],
INTERSECT (
ALL ( 'XX'[project & invoice code] ),
VALUES ( 'XX'[projcect & invoice code] )
)
))
Hope you can help me
Helper I
Your formula is missing a closing parentheses with COUNTROWS and you have an extra closing parrentheses at the end of the formula.Â
It also does not make sense to me that you are using COUNTROWS around a measure which only returns a single value. COUNTROWS is useful only with fomulas that would otherwise return a table.
Also not sure you need the ALL instead of VALUES.
Tom