Forum Discussion
Grid Matrix Lookup and match
HI paulj1,
You can enter to query editor and use unpivot columns function to transform your table as attribute and value columns.
Then write formula to formual your area column:
Format = VAR temp = ROUNDDOWN ( [Area], 1 ) RETURN IF ( temp IN VALUES ( Grid[Size [m2]]] ), temp, MAXX ( FILTER ( ALL ( Grid ), Grid[Size [m2]]] < temp ), Grid[Size [m2]]] ) )Regards,
Xiaoxin Sheng
Doesnt seem to work. Explaining how i do it in excel...
1. the lookup area numeric may not be in the grid, for example in excel when I use VLOOKUP by putting TRUE at the end it will return the nearest. e. 3.2 will use row with 0, 5.22 will use row of 5.2, 5.99 will use row of 5.9
2. Once row is established, then use MATCH to cross reference the column number, eg. 2 will return column number 3, 3 will return column number 4 etc
3. I use the result of the MATCH function in the VLOOKUP to "pick" the correct result
How do i do this in POWER BI....
I could colapse the table into a simpler table and then lookup.... by combining fields, in POWERBI how do i create this reference table, appending columns below each other ???
My brain hurts....
Thanks in advance for any wise words
Paul
HI paulj1,
You can enter to query editor and use unpivot columns function to transform your table as attribute and value columns.
Then write formula to formual your area column:
Format = VAR temp = ROUNDDOWN ( [Area], 1 ) RETURN IF ( temp IN VALUES ( Grid[Size [m2]]] ), temp, MAXX ( FILTER ( ALL ( Grid ), Grid[Size [m2]]] < temp ), Grid[Size [m2]]] ) )Regards,
Xiaoxin Sheng
Thank you Xiaoxin Sheng :smileyhappy:
i had done the unpivot, and was using a complicated if statement, but your solution is better.