Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
haassiej
Helper I
Helper I

Can'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:

journal.PNG

 

 

Procurement:

procurement.PNG

 

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.

error.PNG

 

For this purpose, I tried to create a "bridge" table with a DISTINCT value for the IDs.

 

bridge.PNG

 

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.

 

 

 

1 ACCEPTED SOLUTION

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:

 

LookupWithMultipleIDs.png

 

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

www.PowerPivotPro.com

 

 

View solution in original post

8 REPLIES 8
haassiej
Helper I
Helper I

@P3Tom: Do you happen to know how to get a measure belonging to the NUM from Table B without a relationship between Tables A and B.

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.

 

PictureRef.png

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

 

Hi 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

 

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

www.powerpivotpro.com

haassiej
Helper I
Helper I

@P3Tom already tried with a lookupvalue and get this error:
error2.PNG

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:

 

LookupWithMultipleIDs.png

 

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

www.PowerPivotPro.com

 

 

Many thanks @P3Tom

P3Tom
Helper I
Helper I

Consider LOOKUPVALUE

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.