Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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])
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
Solved! Go to 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 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.
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
User | Count |
---|---|
17 | |
16 | |
14 | |
13 | |
12 |
User | Count |
---|---|
17 | |
14 | |
12 | |
10 | |
9 |