Forum Discussion

charleshale's avatar
charleshale
Continued Contributor
2 years ago
Solved

Bug in Lookupvalue() ?

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

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    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.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    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's avatar
      charleshale
      Continued Contributor
      2 years ago

      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