Forum Discussion

diederd's avatar
diederd
Helper II
6 years ago
Solved

Using LookUpValue in a measure (including switch)

Hello PBI Community.   I've recently come across an issue and I'm hoping the following is possible. Whilst I have two tables; related by means of the <identifier> column - the idea is that I'm wish...
Need Help
Show and Tell
  • Icey's avatar
    Icey
    6 years ago

    Hi diederd ,

     

    There are duplicate rows in your EPP table. So, lookupvalue() will return multiple values in a cell. This is not allowed.

    Try this:

    versionInstalled =
SWITCH (
    SELECTEDVALUE ( tblSoftware[packageName] ),
    "EPP", CALCULATE (
        FIRSTNONBLANK ( EPP[Application Version], 1 ),    -----------return the first not blank value
        FILTER (
            ALLSELECTED ( EPP ),
            EPP[Identifier] = MAX ( 'Active Devices'[Identifier] )
        )
    )
)

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Super User
6 years ago

diederd , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

You can move data from one table to another like the example other than lookup

City Name in sales table= maxx(FILTER(geography,geography[City Id]=Sales[City Id]),geography[City])

 

You can add conditions as per need

  • diederd's avatar
    diederd
    Helper II
    6 years ago

    amitchandak Thank you for your prompt repy. I have included a sample PBIX file. 

     

    Much appreciated.

  • Aimeeclaird's avatar
    Aimeeclaird
    Helper IV
    5 years ago

    Hi amitchandak - apologies, appreciate your original response was a while ago now but I wondered if you could help me please. 

     

    I am trying to find a way to 'look up' a value in a different row of the table based on criteria. I've attached an example file which is a similar structure to my data. 

     

    Dogs and Cats example data.xlsx

     

    I am trying to write something in a custom/calculated column along the lines of:

     

    if [Sale Type] = "Dog" then FIND [ID] = [ID] and [Sale Type] = "Cat" then return the [Date of Sale] however I'm not sure of the correct way to write this in DAX. 

     

    Any help would be appreciated as your suggestion on this thread seems relevant to my problem, however i'm struggling to adapt it.