Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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):
Each class can have multiple transport codes (table; CLASS)
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:
So I now want to get this as a new table in the PowerBI dashboard:
How can I perform this IF exercise with the combined lookup?
Thanks in advance!
Solved! Go to Solution.
Hi @zipke
In Edit Queires
step1. in "ITEM' table, merge queries as below
step2. expand column"CLASS.1"
step3.merge three columns
step4. in "transport" table, merge three columns("items", "class","transport code") into one column, just as step3
step5.in "transport" table, merge queries
then expand "ITEM" column to get "ITEM.CLASS.1.transport code" column
step6. adda conditioanl column
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.
Hi @zipke
In Edit Queires
step1. in "ITEM' table, merge queries as below
step2. expand column"CLASS.1"
step3.merge three columns
step4. in "transport" table, merge three columns("items", "class","transport code") into one column, just as step3
step5.in "transport" table, merge queries
then expand "ITEM" column to get "ITEM.CLASS.1.transport code" column
step6. adda conditioanl column
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.
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:
To this, and it goes up to row 59.
Thanks in advance!
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!
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")
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.
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.
Waiting for your valuable solution!
Thank you
ng
So, do you have a table of Class codes between your first and second tables? Or do you have a many-to-many relationship?
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
104 | |
77 | |
71 | |
51 |
User | Count |
---|---|
146 | |
107 | |
106 | |
89 | |
65 |