DAX Lookupvalue FORMULA

Name is Josh, currently as a Planner at a Minerals company in NSW Australia. I’m interested to learn Power BI and it looks like i need some help.

My report has the below context, with 1000+ orders a target QTY and Target Hours per week. I need to categorize these orders by their Required Type. Example: SALES ORDER, PUMP, INTER COMPANY, SAFETY STOCK.

I am planning to create a virtual table that look as per the below pic. Which is a combination of 2 other tables provided by other departments. I created this one with the New Table icon in PB for this purpose.


Basically what I need is to grab an order from the context table above, find in the virtual table and return the Required Type.

I have use the below formula “LOOKUPVALUE” to return the Required Type in column because this is the only way i have been able to do this. But i think using columns and LOOKUPVALUE have slowed the performance of report.

This pattern is very common for my current projects and would like some guidance if you could please help me out?

This is my Dashboard so far.

You can do it by simply creating a relationship on the identified feel in the relationship view rather than adding a custom column. This will be more efficient and flexible way of doing it and will not increase your model size.

Hi Josue, welcome to the forum.

I think you need to use a table with relationship to achieve this. As this will avoid unnecessary rendering of data in your model. Also, do use slicers with more caution as each slicer selection causes full scans of each other slicer and the visualizations on the page.

Please read this blog for more details.

Hi Piryani Mohammed,

Thank you for the reply.

I would like to have one Measure that does the following.

  1. From the context table (which includes the Order number, Target QTY and Target
    Hours) grab the order number fill, and then.
  2. Check in the Sales order if the order number exist in it, if it does then return the
    Requirement type. If not then
  3. Check in the Safety Stock table if it exist in it. if it does then return the
    Requirement type. If not then
    4 If is none of the above then the Requirement Type is something else

This are my current relationships. Number 2 is a supporting table but i could change if required.

ill have a look to see if i can reduce the number of slicers and see how much it improves performance.