Forum Discussion
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 | ||
| 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 |
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.
5 Replies
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.Thank you so much for your help and this is exactly I looking for it.
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
600Saxon10 , 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.
