Forum Discussion
Helper ICan't join tables, how retrieve value from another table?
Hi all,
I try to extract a value from a table in power bi, only that does not work.
I have 2 tables: Journal and Procurement
Journals:
Procurement:
I would like to get "NUM" from Procurement and add to Journal's data if the IDs match.
These 2 tables can not connect because some IDs occur more often.
For this purpose, I tried to create a "bridge" table with a DISTINCT value for the IDs.
If I want to merge ID from "bridge" and other data from "Journal" or "Procurement", it's going wrong. The tables Journal and Procurement don't have a relationship with each other.
Can anyone help me solve this or help to do this with an other option/solution?
In summary: I want to add "NUM" from Procurement to other data from the other table (Journal) when IDs match.
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
8 Replies
P3TomConsider LOOKUPVALUE
- P3Tom
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
- P3Tom
CALCULATE, the measure name from the other table, and INTERSECT have been used to do that. For example, the following will work in the PBIX linked earlier (although the second measures may have the longest name ever thought of):
First, a measure against the Data table.
Row Count for Data Table = COUNTROWS ( Data )
Now, with Items[Item ID] on Rows of a matrix, add a reference to this measure:
Using a Measure from the Data Table with the Item ID Column from the Items table on Rows =
CALCULATE (
[Row Count for Data Table],
INTERSECT (
VALUES ( Data[Item ID] ),
VALUES ( Items[Item ID] )
)
)How many INTERSECTS you will need depends on how many filters are in your matrix.
Paramter position in INTERSECT will make a difference. Notice I am using the VALUES from the filter context of the matrix as the second parameter of INTERSECT.
Tom
wwww.PowerPivotPro.com
haassiejHi 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