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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
zipke
Helper I
Helper I

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
v-juanli-msft
Community Support
Community Support

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.

View solution in original post

9 REPLIES 9
v-juanli-msft
Community Support
Community Support

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.

@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!

Anonymous
Not applicable

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

 

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

 

Thanks a lot Maggie!

 

 

Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Anonymous
Not applicable

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

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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