Forum Discussion
DAX Lookupvalue in same table based on measure
Hi,
I have a simple table called QUOTEBR with two columns "Quote" and "INDEX."
There are currently 4 values in this table as follows:
| Quote | INDEX |
| Q1 | 1 |
| Q2 | 2 |
| Q3 | 3 |
| Q4 | 4 |
I have created two measures on this table:
Rand = RANDBETWEEN(1,MAX(QUOTEBR[Index]))
Quote = LOOKUPVALUE(QUOTEBR[Quote],QUOTEBR[Index],QUOTEBR[Rand])
I am then placing the Quote measure in a Card visual which I had hoped would update to a different quote each time the page was changed to.
The problem I am having, is that the Quote measure keeps throwing the following error: "Calculation error in measure 'QUOTEBR'[Quote]: A table of multiple values was supplied where a single value was expected."
If I subsequently change something on the page, say update another filter, the Rand measure updates then the error resolves and sometimes it returns the expected quote, and sometimes it returns "(blank)".
I would appreciate if someone can let me know if there is something wrong with my measures or a better way to accomplish this.
How about something like this:
Random Quote = VAR Rand = RANDBETWEEN ( 1, MAX ( QUOTEBR[Index] ) ) RETURN CALCULATE ( MAX ( QUOTEBR[Quote] ), QUOTEBR[Index] = Rand )
4 Replies
How about something like this:
Random Quote = VAR Rand = RANDBETWEEN ( 1, MAX ( QUOTEBR[Index] ) ) RETURN CALCULATE ( MAX ( QUOTEBR[Quote] ), QUOTEBR[Index] = Rand )AkhilAshok Thanks, I used this and it has been working for the last few days without a problem. I have to assume that the issue with my two measure way was in how the random number was being passed from one measure to the other, possibly being recaluclated in the process.
Hi benisanders ,
The measures works well on my side, could you share a sample pbix file if possible?
Regards,
Jimmy Tao
Hi, I have posted a sample file here Google Drive. I have set up two sets of the measure one where i explicitly convert the RAND measure into an INT.
If when you open the file both of the Quote boxes work, then if you toggle between the options in the column1 filter to the left of the page you should get the issue.
One other odd thing i noticed while setting this up. it appears that even when the quotes do appear they do not seem to match the related quote in the table. as you can see in the below image the Rand2 measure returned the number 2, however the quote displayed is for 3. It seems like the measure is providing a different random number to the lookup measure than it is when it is displayed.
