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
paulj1
Helper II
Helper II

Grid Matrix Lookup and match

Hello, help me please, my brain hurts......

 

I have a product table, extract below, each product has a size (4.68) and a quality grade (B)

 

Trying to do a calculated column to do a lookup on the grid, rounding down 4.68 to 4.6, and looking in column B, returning the cardinal intersect value of 55.

 

This must be able to be done in Power BI, help please.

 

Thanks in advance

 

Paul

 

 

Lookup1.JPGGrid_LI.jpg

 

1 ACCEPTED SOLUTION

HI @paulj1,

 

You can enter to query editor and use unpivot columns function to transform your table as attribute and value columns.

Power Query Unpivot Scenarios

 

16.gif

 

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]]] )
    )

3.PNG

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
paulj1
Helper II
Helper II

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....

 

 

 

Capture3.JPG

 

 

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 ???Capture4.JPG

 

 

 

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.

Power Query Unpivot Scenarios

 

16.gif

 

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]]] )
    )

3.PNG

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thank you Xiaoxin Sheng  Smiley Happy

 

i had done the unpivot, and was using a complicated if statement, but your solution is better.

v-shex-msft
Community Support
Community Support

HI @paulj1,

 

I think lookupvalue with rounddown functions will suitable for your requirement, please take a look at below formula.

 

Calculate column on Product table:

Lookup =
LOOKUPVALUE ( Grid[B], Grid[Hide Size], ROUNDDOWN ( Product[area], 1 ) )

 

Reference:

ROUNDDOWN Function (DAX)

LOOKUPVALUE Function (DAX)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.