cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zipke Frequent Visitor
Frequent Visitor

Lookup of a value in a list of values defined for that value

Hi all,

 

I need to create a table that performs a lookup in another table and then checks if this value can be found in a list of values specific for this item. Please find below a screenshot to make it more understandable.

 

I have a set of items all linked to one Class (table; ITEM):
item.JPG

 

 

 

 

 

 

 

 

 

 

 

 Each class can have multiple transport codes (table; CLASS)
class.JPG

 

 

 

 

 

 

 

 

 

 

 

 

I have a table in wich the users filled in the transport code manually. I now need to verify if the combinations are possible.

 

These are the Transport codes the users filled in:
users.JPG

 

 

 

 

 

 

 

 

 

 

 

So I now want to get this as a new table in the PowerBI dashboard:

result.JPG

 

 

 

 

 

 

 

 

 

How can I perform this IF exercise with the combined lookup?

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Lookup of a value in a list of values defined for that value

Hi @zipke

In Edit Queires

step1. in "ITEM' table, merge queries as below

17.png

 

step2. expand column"CLASS.1"

18.png

 

step3.merge three columns

19.png

step4. in "transport" table, merge three columns("items", "class","transport code") into one column, just as step3

 

step5.in "transport" table, merge queries

20.png

then expand "ITEM" column to get "ITEM.CLASS.1.transport code" column

 

step6. adda conditioanl column

21.png

 

Best regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

9 REPLIES 9
Super User
Super User

Re: Lookup of a value in a list of values defined for that value

So, do you have a table of Class codes between your first and second tables? Or do you have a many-to-many relationship?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


zipke Frequent Visitor
Frequent Visitor

Re: Lookup of a value in a list of values defined for that value

The first table is 1:1, the second table is many to many.

 

The main table for me is the third table, the table that is filled in by the users. This third table is an excel file I load into powerBI.

I then want to lookuo if the entered combinations are possible: item-transport combination with the use of the code they belong to.

 

Do you understand what I mean?

Super User
Super User

Re: Lookup of a value in a list of values defined for that value

Something like this should work:

 

Column = 
VAR __tc = Users[Transport Code]
VAR __class = Users[Class]
VAR __table = FILTER(ALL(Class),'Class'[Class] = __class && 'Class'[Transport Code] = __tc)
VAR __count = COUNTX(__table,[Transport Code])
RETURN
IF(ISBLANK(__count),"NOT OK","OK")

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


zipke Frequent Visitor
Frequent Visitor

Re: Lookup of a value in a list of values defined for that value

It works for some, but not for all apparently.

 

I get that item AAA is NOK, but it should be OK. And I get that item FFF is NOK and it should also be OK.

Community Support Team
Community Support Team

Re: Lookup of a value in a list of values defined for that value

Hi @zipke

In Edit Queires

step1. in "ITEM' table, merge queries as below

17.png

 

step2. expand column"CLASS.1"

18.png

 

step3.merge three columns

19.png

step4. in "transport" table, merge three columns("items", "class","transport code") into one column, just as step3

 

step5.in "transport" table, merge queries

20.png

then expand "ITEM" column to get "ITEM.CLASS.1.transport code" column

 

step6. adda conditioanl column

21.png

 

Best regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

zipke Frequent Visitor
Frequent Visitor

Re: Lookup of a value in a list of values defined for that value

Thanks a billion for your detailed explanation!! I woudn't have gotten that far without the screen shots!!

 

Thanks a lot Maggie!

 

 

ng2018 Frequent Visitor
Frequent Visitor

Re: Lookup of a value in a list of values defined for that value

Hi All

 

Need some clarification on how to achieve the same scenario in a direct query.

when Flag is updated with Yes and if user logins all Yes SID values from both table only should display to a user.

In RLS-created role email=userprinciplename. 

 

capture_20190129212040.pngWaiting for your valuable solution!

 

Thank you

ng

zipke Frequent Visitor
Frequent Visitor

Re: Lookup of a value in a list of values defined for that value

@v-juanli-msft

 

I have a question concerning the "merge" option, what exactly does it do? In your exercise the merged and expanded table was still pretty small.

 

I am testing it in my proper file now and this expanded table is HUGE. There is not issue, your way-of-working still does the trick, but I am trying to understand what it is showing me? So many null values, where do they come from?

 

I go from here:

Table1.JPG

 

 

 

 

 

 

To this, and it goes up to row 59.

Table2.JPGTable3.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks in advance!

Highlighted
ng2018 Frequent Visitor
Frequent Visitor

Re: Lookup of a value in a list of values defined for that value

HI 

 

 

I would appreciate your response and for your solution posted, the issue is resolved and created a flag table from DB end and added sid column and sid status in it and given join with another table. 

 

I Apologies there was little change in requirement last minute so managed to resolve it.

 

Thankyou,

NG