Forum Discussion

Kitty-SD's avatar
Kitty-SD
Frequent Visitor
3 years ago
Solved

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...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

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

     

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

     

    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!

     

v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
3 years ago

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

 

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

 

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!