Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
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
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.
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]
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |