Lookup by filter by column (New calculated column req)

[Saxon10]

I have a two tables are data and report.

In data table contain item and id no. Each item contain two different id number one is 200 and 300.

In report table item contain unique value.

If item is exist according to the id (300/200) in data table then true and if not false.

The item column are common in-between two tables.

REPORT TABLE
Item	RESULT Id No 200	RESULT Id No 300
12	TRUE	TRUE
123	TRUE	TRUE
234	TRUE	TRUE
345	TRUE	TRUE
456	TRUE	TRUE
567	TRUE	TRUE
678	TRUE	TRUE
789	TRUE	TRUE
456	FALSE	FALSE
600	FALSE	FALSE

.

DATA TABLE
Item	Id No
12	200
123	100
12	300
234	300
234	200
345	200
345	300
456	200
456	300
567	200
567	300
678	200
789	300
678	300
789	300

[v-yalanwu-msft · Accepted solution]

Hi @Saxon10 ，

According to you description，you could create columns as follows:

RESULT Id No 200 =
VAR _a =
    SUMMARIZE ( FILTER ( 'DATA', [Id No] = 200 ), [Item] )
RETURN
    IF ( [Item] IN _a, "TRUE", "FLASE" )

RESULT Id No 300 =
VAR _a =
    SUMMARIZE ( FILTER ( 'DATA', [Id No] = 300 ), [Item] )
RETURN
    IF ( [Item] IN _a, "TRUE", "FLASE" )

 Another request:

RESULT1 Id No 200 =
VAR _new =
    SUMMARIZE ( FILTER ( 'DATA', [Id No] = 200 ), [Item] )
RETURN
    IF ( [Item] IN _new, 200 )

RESULT1 Id No 300 =
VAR _new =
    SUMMARIZE ( FILTER ( 'DATA', [Id No] = 300 ), [Item] )
RETURN
    IF ( [Item] IN _new, 300 )

In addition, when [item]=123, Because [Id No] does not contain (200/300), so it may be “False” ?

IF so ,The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Saxon10]

Thank you so much for your help and this is exactly I looking for it. 

[Saxon10]

One more request

Instead off true or false according to the item. I would like to expect actual value.

I am looking for new calculated column.

I am try to attempt lookup value and calculated column but it’s not working.

REPORT TABLE
Item RESULT 1 Id No 200 RESULT 1 Id No 300
12 200 300
123 200 300
234 200 300
345 200 300
456 200 300
567 200 300
678 200 300
789 200 300
456
600

[amitchandak]

@Saxon10 , if this 200 and 300 in columns then you have to unpivot the table

https://radacad.com/pivot-and-unpivot-with-power-bi

Then you have to create a column like this in both tables and join

key = [Item]& " " & [Id No]

[Saxon10]

Thanks for the reply. Is there any option in order to achieve my final output.