Using LookUpValue in a measure (including switch)

[diederd]

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 wishing to perform a lookup of the software version in Table A based on the device name in Table B. 

Table A     Contains details on the software package (EPP)

Table B     Contaings details on the hardware device (including device name, hardware status, make, model)

Based on a SWITCH command; user selects software type and then I'm hoping to have a LookUpValue to provide the software version (Table A) of the selected device (Table B).

versionInstalled = SWITCH(SELECTEDVALUE(tblSoftware[packageName]),
                              "EPP",
LOOKUPVALUE('EPP'[Application Version],
'EPP'[Identifier],
'Active Devices'[Identifier]))

I have the following measure; but it doesn't appear to be working as intended:

Are you able to provide any guidance as to where I'm going wrong? The end table should look something like this:

In the space of the blue arrow should be the software version of the selected software package.

Hopefully the above all makes sense and that somebody could help shed some light on my DAX query.

[Icey · Accepted solution]

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.

[diederd]

Icey.

Thank you for your response and apologies for the delay in my response.

Having tried your solution; this works great however would it be possible to tweak the query to return only specific applications versions? I'm trying to create a query which returns all software products that are non-compliant based on an installed version.

Hope that makes sense..

Thank you again for your help regarding this issue.

[amitchandak]

@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

[Aimeeclaird]

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. 

[diederd]

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

Much appreciated.