Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
diederd
Helper II
Helper II

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 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.

 

 

 

 

Capture_1.JPG

 

 

Capture.JPG

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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

epp.PNG

 

 

Best Regards,

Icey

 

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

View solution in original post

5 REPLIES 5
Icey
Community Support
Community Support

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

epp.PNG

 

 

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.

 

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
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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. 

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

 

Much appreciated.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.