Forum Discussion

John_Peter's avatar
John_Peter
Regular Visitor
6 years ago
Solved

DAX Lookup value

I need help ðŸ˜‰   I have 3 tables including Sales, Customers and Cost per product between 2 dates. I would like to add a calculated column in the sales table which according to the Currency of the ...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi, John_Peter 

    Try to create calculated column as below:

    Column = [Amount] - (CALCULATE(SUM('Cost'[Value]),FILTER('Cost','Cost'[Attribute] = "Cost"&LOOKUPVALUE('Customers'[Currency],Customers[Customer],'Sales'[Product]) && 'Sales'[Date]>='Cost'[From] && 'Sales'[Date]<='Cost'[To]))*[Qty])

    It shows as below:

     

     

    Here is a demo:
    pbix attach 


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



Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

So, you can use LOOKUPVALUE for the currency. You would then do something like:

 

Column = 
  VAR __Currency = LOOOKUPVALUE('Customers'[Currency],'Customers'[Customer],'Sales'[Customer])
  VAR __Table = 
    FILTER(
      'Cost',
      'Sales'[Date] >= 'Cost'[From] && 
        'Sales'[Date]<='Cost'[To] && 
          'Sales'[Product] = 'Cost'[Product]
    )
RETURN
  SWITCH(__Currency),
    "USD",MAXX(__Table,[CostUSD]),
    "CAD",MAXX(__Table,[CostCAD]),
    BLANK()
  )

 

John_Peter's avatar
John_Peter
Regular Visitor
6 years ago

Greg_Deckler

Thanks for the response, 

 

I am brand new in the world of PowerBI, can you tell me if it is better to create a table to put all my calculated Column? if so how do i go about creating this dimension or table?

 

I really appreciate your help.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    I would generally not create a separate table for just calculated columns. You generally put calculated columns into your fact table or the table where it makes the most amount of sense. 

     

    For you other question, you create new tables by using the New Table button in the ribbon. Depends on the version of the ribbon you are using where this is located. In the new ribbon, this is found under Modeling and it is in the Calculations area.