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
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.
Solved! Go to 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.
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.
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.
@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
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
117 | |
109 | |
109 | |
93 | |
69 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
94 |