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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
th3h0bb5
Resolver II
Resolver II

Lookup Returning only Partial Match

I have two tables: General Ledger (GL) and CustomerOrderHeader (COH). COH has a unique Primary Key. GL has the same value as a Foreign Key.

 

When I write a LOOKUP() Calculated Column, it only returns a value one time, even though there are multiple matches. The screenshot below captures why I'm confused. For Key 7677, I should see my LOOKUP() value every time. Why on earth does it only pull across a value for the first row? I've trimmed and cleaned both the PK and FK. They're both saved as Text as well.

Error Message.png

 

 

1 ACCEPTED SOLUTION

Hi @th3h0bb5 ,

 

I guess that there may be hidden characters (such as spaces) in the CustomerOrderHeaderID column in GL table, causing the values are not exactly match. It is recommended to check whether there are multiple "7677" in the drop-down list of the column. Or try the following formula to see if all values can be returned. 

 

image.png

Column = 
CALCULATE(
    MAX('CustomerOrderHeader'[CUSTOMER NAME]),
    FILTER(
        'CustomerOrderHeader',
        'CustomerOrderHeader'[CustomerOrderHeaderID] = EARLIER('General Ledger'[CustomerOrderHeaderID])
    )
)

 

If there are multiple "7677", or the formula only returns part of the value, then you can try the following ways.


1. Change the data types of these two columns to Whole number to eliminate hidden characters.


2. Or use fuzzy matching to perform the merge in Power Query Editor.

 

vkkfmsft_0-1629164370840.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

Your LOOKUPVALUE() formula should be a written as a calculated column formula in the General Ledger table - not as a measure.  


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

 

If you reread my original post, you'll see I said "When I write a LOOKUP() Calculated Column...", so I'm already doing that.

Hi,

From the image that you have shared, it looks like a measure.  Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

 

Thanks for the feedback. Since the output of a measure can only be a scalar value within a filter context (that is, an aggregate), and the output of my DAX (i.e. CustomerOrderHeader[CUSTOMER NAME]) is a non-aggregated column reference, it would be impossible for this DAX to be a measure. As a measure, I'd use something like:

MeasureVersion=
CALCULATE(
     MIN(CustomerOrderHeader[CUSTOMER NAME]),
     USERELATIONSHIP(

          CustomerOrderHeader[CustomerOrderHeaderID],

          GeneralLedger[CustomerOrderHeaderID]

          )

     )

 

Unfortunately, I've tried that as well and it returns the same results (see my response to SevenHills below).

 

I'm afraid I can't send a link to the PBIX file. It's client data and about 1.2 GB in size. I've tried to recreate the failure on dummy data (which I would be able to send you a link to) but it works fine when I do it there, which just adds to my confusion. If my DAX code works fine on fake data, what on earth is wrong with my columns that prevents it from working on my actual data?

Hi @th3h0bb5 ,

 

I guess that there may be hidden characters (such as spaces) in the CustomerOrderHeaderID column in GL table, causing the values are not exactly match. It is recommended to check whether there are multiple "7677" in the drop-down list of the column. Or try the following formula to see if all values can be returned. 

 

image.png

Column = 
CALCULATE(
    MAX('CustomerOrderHeader'[CUSTOMER NAME]),
    FILTER(
        'CustomerOrderHeader',
        'CustomerOrderHeader'[CustomerOrderHeaderID] = EARLIER('General Ledger'[CustomerOrderHeaderID])
    )
)

 

If there are multiple "7677", or the formula only returns part of the value, then you can try the following ways.


1. Change the data types of these two columns to Whole number to eliminate hidden characters.


2. Or use fuzzy matching to perform the merge in Power Query Editor.

 

vkkfmsft_0-1629164370840.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

sevenhills
Super User
Super User

Just curious ... what you are getting for RELATED(CustomerOrderHeader[CUSTOMER NAME]) ? (assuming u have relationship between GL and COH)

The entire model is quite complex with about 150 tables, so I haven't edited all the relationships necessary to test that hypothesis. However, I did use an inactive relationship and CALCULATE() with USERELATIONSHIP(). Frustratingly, it produces the same result as the screenshot above. The DAX code for that is below. I've also successfully used a SQL LEFT JOIN on the PK and FK and it works fine. It's like there's something specific to Power BI that causes the join to fail.
CalculateVersion.png

UseRelationship, as per my knowledge, uses the existing relationship from the model. Hope this helps!

 

I tried to replicate

sevenhills_0-1628636242905.pngsevenhills_1-1628636264842.png

sevenhills_3-1628636392210.png

 

Both my measures worked

 

Lookup = LOOKUPVALUE( CustomerOrderHeader[Customer Name], CustomerOrderHeader[CustomerOrderHeaderID], SELECTEDVALUE(GeneralLedger[CustomerOrderHeaderID]))

Lookup2 = CALCULATE( 
  MIN(CustomerOrderHeader[Customer Name])
  , TREATAS( CALCULATETABLE(VALUES(GeneralLedger[CustomerOrderHeaderID]), GeneralLedger[CustomerOrderHeaderID]), CustomerOrderHeader[CustomerOrderHeaderID])
)

 

 

sevenhills_4-1628636620357.png

 

 

sevenhills_5-1628636788041.png

 

 

I think in case of virtual relationships, we normally tend to use TREATAS. 

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

 

 

(I have to multiple edits to this reply to get you the DAX. )

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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