Forum Discussion

Saxon10's avatar
Saxon10
Post Prodigy
5 years ago
Solved

Calculated column contain multiple result

 

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

 

Need Help
  • parry2k's avatar
    parry2k
    5 years ago

    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's avatar
    Anonymous
    5 years ago

    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.

     

     

5 Replies

  • Saxon10's avatar
    Saxon10
    Post Prodigy
    5 years ago

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

     

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    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's avatar
      Saxon10
      Post Prodigy
      5 years ago

      Hi, 

       

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Saxon10
      Post Prodigy
      5 years ago

      Thanks for your reply and simple way of your solution.