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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Cubist
Advocate II
Advocate II

LookupValue error - Is this a DAX Bug?

Can anyone help say if this simple code is a DAX bug or my mistake?

 

When trying to use LOOKUPVALUE, I'm getting two errors:

  • Returning blank instead of the correct values
  • Circular reference error

 

Attached is a .pbix showing the problem, stripped down to almost nothing.  To reproduce the problem, load the .pbix file into Power BI Desktop, go to the Data view, click on "DateTable2".  Then notice:

 

  1.  Column "Lookup1" shows a value only for the first row.  Why are the other rows blank for this column?
  2.  Click on column "Lookup2", there is a circular dependency error, but I see no cycle.

 

Actual file attached

 

Column Definitions    (DateTable[Index] does have the value 1097)

   LookUp1 = LOOKUPVALUE(DateTable[Index], DateTable[Index], 1097)
   LookUp2 = DateTable2[LookUp1]

 

File showing problem: LookupIssue.pbix

 

 

Error in simple column defError in simple column def

 

 

 

4 REPLIES 4
Sean
Community Champion
Community Champion

 

LookUp1 = LOOKUPVALUE(DateTable[Index], DateTable[Index], DateTable2[Index] )

 

You are hardcoding a number 1097  - If you enter 1098 you'll get a value only on the 2nd row...

https://msdn.microsoft.com/en-us/library/gg492170.aspx

 

To get rid of the circular dependency in your case change the cross filter direction to single!

https://www.sqlbi.com/articles/understanding-circular-dependencies/

>>To get rid of the circular dependency in your case change the cross filter direction to single!

 

Regarding the circular reference -

 

The cross filter direction can't be changed to single, because I need to dynamically change the axis on the bar chart visual based on a slicer. To my knowledge, the only way to do this is with a "bridging table" technique that a few people have come up with. One form of that is described here: http://tinylizard.com/dynamically-changing-chart-axis.

 

If there is another way to do this without bi-directional relations, that would be great to find out.

>>You are hardcoding a number 1097  - If you enter 1098 you'll get a value only on the 2nd row...

 

No, I don't think so.  The LOOKUPVALUE function has three parameters which tell DAX to do this:


    Result column           Once you find the row I'm searching for, return the value in this column
    Search column          To find the row I'm searching for, check the values in this column for match

    Value to search for    Look at rows in the Search Column for this value.  If you find it, return the value in Result column.

 

Hardcoding the Value just means search for the same thing always.  It means the entire column in the screen shot should be filled with the same result value.  It's just a constant for testing.

 

To see why I think it's a bug, load the .pbix file, and rename the column "DateTableUnPivotId".  Like magic the column fills up with values.

 

 

Sean
Community Champion
Community Champion

If cross-filtering can't be changed why don't you get the value from the BaseDate table? (no relationship needed!)

LookUp1 = LOOKUPVALUE ( BaseDate[Index], BaseDate[Index], 1097 )

LookUp2 = DateTable2[LookUp1]

LOOKUPVALUE.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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