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.

Icey's avatar
Icey
Community Support
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.

  • diederd's avatar
    diederd
    Helper II
    6 years ago

    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.