Calculated column contain multiple result

[Saxon10]

I have two tables are, Data and report, In data table contain item and supplier and report table contain item.

Both tables the item column is unique.

Data Table:

ITEM	SUPPLIER
112OP	A1
345YO
567	A2
345Y1

The supplier some time is blanks according to the item in data table. Example - 345YO AND 345Y1. 

Report Table:

ITEM	DESIRED RESULT FOR SUPPLIER
112OP	A1
345YO
567	A2
56890	NA
25645783	NA
XX12	NA
345Y1

If item matched in data table then return supplier (Example - 112OP, 567, 345YO, 345Y1) and if not then return “NA” according to the item (Example - 56890, 25645782, XX12, 345Y1).

I am trying to achieve what are the items is blanks and which item not available from data table into report table.

Can you please advise

[parry2k · Accepted solution]

@Saxon10 add a new column in reporting table using the following expression, assuming these tables have a relation on Items  column

Supplier = 
VAR __supplier = CALCULATE ( MAX ( 'DataTable'[SUPPLIER] ),  RELATEDTABLE ( 'DataTable' ) )
RETURN
COALESCE ( __supplier, "NA" )

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous · Accepted solution]

Hi @Saxon10 ,

Based on your description, you can create a column as follows.

Column = LOOKUPVALUE(Data[SUPPLIER],Data[ITEM],[ITEM],"NA")

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Saxon10]

Thanks for your reply and simple way of your solution. 

[parry2k · Accepted solution]

@Saxon10 add a new column in reporting table using the following expression, assuming these tables have a relation on Items  column

Supplier = 
VAR __supplier = CALCULATE ( MAX ( 'DataTable'[SUPPLIER] ),  RELATEDTABLE ( 'DataTable' ) )
RETURN
COALESCE ( __supplier, "NA" )

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Saxon10]

Hi, 

Sorry for the late response. Your solution working well. Thanks for your time and help. 

[Saxon10]

I am looking for DAX (new calculated column option) in order to achieve my result.