Bug in Lookupvalue() ?

[charleshale]

I am experiencing a result wherein a simple lookupvalue() for the exact same value sometimes returns blank and sometimes doesnt.   I have never seen this before.

Here's the detail:  I am adding a simple column via AddColumn that goes to mapping table (Json), looks for the ID number (Json[id]) that matches ('Stripe Raw'[Originating Patch]), and returns the name in the same row of the Json table column [wbs2.1].  

OriginatingPatch_ =   --Adds column into Stripe Raw table of Patch name (the "WBS")
LOOKUPVALUE(Json[WBS2.1], Json[id], 'Stripe Raw'[Originating Patch])

Both Json[Id] and Stripe Raw[Originating Patch] are integers and both columns have blanks within their rows.   Simple, right?

 

Yet here is a snapshot of the sample results, showing for example 'Stripe Raw'[Originating Patch] = 622.  This matches Json[ID] = 622 and the corresponding Json[WBS2.1] is PA_Bethlehem.   As you can see, sometimes PA_Bethlehem gets returned and sometimes blanks get returnded for the same integer.

I tried normalizing the formats with INT and FORMAT without any change in outcome.   The following also does not change the outcome: 

Try3 = 
CALCULATE(
    FIRSTNONBLANK(Json[WBS2.1], 1),
    FILTER(
        Json,
        Json[Id] = 'Stripe Raw'[Originating Patch]
    )

Note that Debug_OrigPatch =  'Stripe Raw' [Originating Patch]  returns the correct column value (622), but anything like the  Debug_OrigPatch = selectedvalue('Stripe Raw'[Originating Patch]) returns only blanks.

Any thoughts? I have never had this issue. Given the amount of time I've spent on it, either I am doing something incredibly dumb that I am missing or it's a bug. Thank you Charlie

[Greg_Deckler · Accepted solution]

@charleshale So LOOKUPVALUE will return BLANK if more than one row is returned. I've encountered some issues with it honestly which is why I always use MAXX( FILTER( ... ), ... ) instead.

[charleshale]

The greatest @Greg_Deckler.  You have solved my problem with your insight and reflected glory!  So - fantastic point as usual: yes that maxx is better.      Here's what I did, below.

Try7 = 
CALCULATE(
    maxx(Json, json[WBS2.1]),
    FILTER(
        Json,
        Json[Id] = 'Stripe Raw'[Originating Patch]
    )
)

 However, the problem remained the same.

This got me thinking of a totally different solution: adding an all() after calculate.

_OrigPatchNameLkup = 
CALCULATE(
    maxx(json, Json[WBS2.1]),
    FILTER(
        Json,
        Json[Id] = 'Stripe Raw'[Originating Patch]
    ), all('Stripe Raw')
)

This worked.   Somehow there must be a filter context somewhere in my table that was causing the blanks.  I still don't see exactly where, but the column and measure now work.  How interesting!   Thank you for this varsity-level point.

Charlie