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

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.

Reply
Saxon10
Post Prodigy
Post Prodigy

Lookup by filter by column (New calculated column req)

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
ItemRESULT Id No 200RESULT Id No 300
12TRUETRUE
123TRUETRUE
234TRUETRUE
345TRUETRUE
456TRUETRUE
567TRUETRUE
678TRUETRUE
789TRUETRUE
456FALSEFALSE
600FALSEFALSE

.

 

DATA TABLE
ItemId No
12200
123100
12300
234300
234200
345200
345300
456200
456300
567200
567300
678200
789300
678300
789300

 

Saxon10_1-1621495650330.png

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

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” ?

v-yalanwu-msft_0-1621845099403.png

 

IF so ,The final output is shown below:

v-yalanwu-msft_1-1621845099404.png

 

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.

View solution in original post

5 REPLIES 5
v-yalanwu-msft
Community Support
Community Support

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” ?

v-yalanwu-msft_0-1621845099403.png

 

IF so ,The final output is shown below:

v-yalanwu-msft_1-1621845099404.png

 

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.

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

Saxon10
Post Prodigy
Post Prodigy

 

 

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

 

Saxon10_0-1621496097889.png

 

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

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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