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
Kitty-SD
Frequent Visitor

DAX Lookup no exact match

Hi,

 

I'm working on a project looking at the commission we receive from suppliers based on the amount of business we send. I need to automate the process of looking up what level of commission has been achieved against each supplier. Usually, I would use a VLOOKUP TRUE formula in Excel, but I'm struggling to get this to work in Power BI.

 

Data:

Table 1

KittySD_2-1670504039849.png

 

Table 2

KittySD_1-1670503950646.png

On table 1, I need to get the level achieved & the commission % available based on the commissionable amount. 
I've tried the following solution for level achieved, but it's returning a blank.

Level Achieved =
VAR Commissionable = SELECTEDVALUE (StoredProcedure[Supplier Due])
VAR Boundary =
    TOPN (
        1,
        FILTER (OverrideAgreements,OverrideAgreements[Target] >= Commissionable ),
        OverrideAgreements[Target], DESC
    )
    RETURN
        MAXX (Boundary,OverrideAgreements[Level])

 

Any help appreciated!

Thanks,

Kitty

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @Kitty-SD 

 

This is probably what you want. 

 

A calculated column

Level Achieved = 
VAR Commissionable = StoredProcedure[Supplier Due]
VAR Boundary =
    TOPN (
        1,
        FILTER (OverrideAgreements,OverrideAgreements[Target] <= Commissionable && OverrideAgreements[SupplierId] = StoredProcedure[SupplierId]),
        OverrideAgreements[Target], DESC
    )
RETURN
    MAXX (Boundary,OverrideAgreements[Level])

vjingzhang_0-1670578045436.png

 

A measure

Level Achieved Measure = 
VAR Commissionable = SELECTEDVALUE(StoredProcedure[Supplier Due])
VAR SupplierId = SELECTEDVALUE(StoredProcedure[SupplierId])
VAR Boundary =
    TOPN (
        1,
        FILTER (OverrideAgreements,OverrideAgreements[Target] <= Commissionable && OverrideAgreements[SupplierId] = SupplierId),
        OverrideAgreements[Target], DESC
    )
RETURN
    MAXX (Boundary,OverrideAgreements[Level])

vjingzhang_1-1670578611155.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

 

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

Hi @Kitty-SD 

 

This is probably what you want. 

 

A calculated column

Level Achieved = 
VAR Commissionable = StoredProcedure[Supplier Due]
VAR Boundary =
    TOPN (
        1,
        FILTER (OverrideAgreements,OverrideAgreements[Target] <= Commissionable && OverrideAgreements[SupplierId] = StoredProcedure[SupplierId]),
        OverrideAgreements[Target], DESC
    )
RETURN
    MAXX (Boundary,OverrideAgreements[Level])

vjingzhang_0-1670578045436.png

 

A measure

Level Achieved Measure = 
VAR Commissionable = SELECTEDVALUE(StoredProcedure[Supplier Due])
VAR SupplierId = SELECTEDVALUE(StoredProcedure[SupplierId])
VAR Boundary =
    TOPN (
        1,
        FILTER (OverrideAgreements,OverrideAgreements[Target] <= Commissionable && OverrideAgreements[SupplierId] = SupplierId),
        OverrideAgreements[Target], DESC
    )
RETURN
    MAXX (Boundary,OverrideAgreements[Level])

vjingzhang_1-1670578611155.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

 

amitchandak
Super User
Super User

@Kitty-SD , a new columnin table one

 

var _max  = maxx(table2, Table1[supplier] = table1[Supplier] && Table1[Supplier due]>=  Table2[Target]),Table2[Target])

return

maxx(table2, Table1[supplier] = table1[Supplier] && Table1[Supplier due]=  _max  ),Table2[Comission %])

 

I'm getting this error!

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