cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

need help in Lookup function

Hi All,

I have data like below table 

Screenshot_7.png

I want to create "Is Predicted" Filed based on the below condition by using M query 

  • If "Actual value" is present in "Possible value" then yes else No

Screenshot_8.png

 

Sample data:

IDActual ValuePossible ValueIs Predicted
ID1ABYes
ID1AAYes
ID1ACYes
ID2EBNo
ID2EANo
ID2ECNo
ID3CBYes
ID3CAYes
ID3CCYes
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: need help in Lookup function

@vengadeshpalani 

 

As a custom column, you can use

"Changed Type" is just the previous step's name. You have to change it according to your last step in the Query Editor

 

=let myID=[ID] in
let mylist=Table.SelectRows(#"Changed Type",each [ID]=myID)[Possible Value]
in
if List.Contains(mylist,[Actual Value]) then "Yes" else "No"

PQy.png

Try my new Power BI game Cross the River

View solution in original post

3 REPLIES 3
v-cherch-msft Super Contributor
Super Contributor

Re: need help in Lookup function

Hi @vengadeshpalani 

You may create a calculated column with DAX as below:

Column = 
VAR a =
    CALCULATETABLE (
        VALUES ( 'Table'[Possible Value] ),
        ALLEXCEPT ( 'Table', 'Table'[ID] )
    )
RETURN
    IF ( 'Table'[Actual Value] IN a, "Yes", "No" )

1.png

Regards,

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

Re: need help in Lookup function

Thanks, 

 

Is it possible in M query editor? 

Highlighted
Super User
Super User

Re: need help in Lookup function

@vengadeshpalani 

 

As a custom column, you can use

"Changed Type" is just the previous step's name. You have to change it according to your last step in the Query Editor

 

=let myID=[ID] in
let mylist=Table.SelectRows(#"Changed Type",each [ID]=myID)[Possible Value]
in
if List.Contains(mylist,[Actual Value]) then "Yes" else "No"

PQy.png

Try my new Power BI game Cross the River

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 310 members 2,784 guests
Please welcome our newest community members: