Forum Discussion

jobf's avatar
jobf
Icon for Helper II rankHelper II
2 years ago
Solved

LOOKUPVALUE with multiple conditions

Hello. I need to create a column that takes values ​​from a column based on more than one condition. Example:

VarietyPeriodBags
P1M120
P2M159
P3M101
P4P90
P5P95


I needed to create a column that showed the variety whose bags were larger in a given period. It would look something like this:

VarietyPeriodBags V + P
P1M120P2
P2M159P2
P3M101P2
P4P90P5
P5P95P5
Need Help
  • uzuntasgokberk's avatar
    uzuntasgokberk
    2 years ago

    Hello jobf,

     

    You can use below DAX formula.

    "

    pbısupportgokberkuzuntas =
    Var maxval_=
    CALCULATE(
    MAX(Tablesil1[Bags]),
    ALLEXCEPT(Tablesil1,
    Tablesil1[Periods]
    )
    )
    RETURN
    LOOKUPVALUE(Tablesil1[Variety],Tablesil1[Bags],maxval_)
    "

    Kind Regards,
    Gökberk Uzuntaş

    📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!

    🔗 Stay Connected:
    📘 Medium |
    📺 YouTube |
    💼 LinkedIn |
    📷 Instagram |
    🐦 X |
    👽 Reddit |
    🌐 Website |
    🎵 TikTok |

  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    jobf 

    you can try this

     

    Column =
    maxx(FILTER('Table','Table'[Period]=EARLIER('Table'[Period])&&'Table'[Bags]=maxx(FILTER('Table','Table'[Period]=EARLIER('Table'[Period])),'Table'[Bags])),'Table'[Variety])
     
     

3 Replies

  • uzuntasgokberk's avatar
    uzuntasgokberk
    Icon for Super User rankSuper User
    2 years ago

    Hello jobf,

     

    You can use below DAX formula.

    "

    pbısupportgokberkuzuntas =
    Var maxval_=
    CALCULATE(
    MAX(Tablesil1[Bags]),
    ALLEXCEPT(Tablesil1,
    Tablesil1[Periods]
    )
    )
    RETURN
    LOOKUPVALUE(Tablesil1[Variety],Tablesil1[Bags],maxval_)
    "

    Kind Regards,
    Gökberk Uzuntaş

    📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!

    🔗 Stay Connected:
    📘 Medium |
    📺 YouTube |
    💼 LinkedIn |
    📷 Instagram |
    🐦 X |
    👽 Reddit |
    🌐 Website |
    🎵 TikTok |

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    2 years ago

    jobf 

    you can try this

     

    Column =
    maxx(FILTER('Table','Table'[Period]=EARLIER('Table'[Period])&&'Table'[Bags]=maxx(FILTER('Table','Table'[Period]=EARLIER('Table'[Period])),'Table'[Bags])),'Table'[Variety])
     
     
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Write this calculated column formula

    Column = LOOKUPVALUE(Data[Variety],Data[Bags],CALCULATE(MAX(Data[Bags]),FILTER(Data,Data[Period]=EARLIER(Data[Period]))),Data[Period],Data[Period])

    Hope this helps.