topic Bug in Lookupvalue() ? in DAX Commands and Tips

Bug in Lookupvalue() ?

charleshale — Thu, 13 Jun 2024 18:03:59 GMT

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

Re: Bug in Lookupvalue() ?

Greg_Deckler — Thu, 13 Jun 2024 19:05:26 GMT

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.

Re: Bug in Lookupvalue() ?

charleshale — Thu, 13 Jun 2024 21:36:41 GMT

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