# LOOKUP ON MULTIPLE CRITERIA IN DAX

• ### Question

• Dear Expert!

First of all thanks for your contribution in making things possible for learners.

I structured a set of tables and made relationships in power pivot to do following things

1. Make targets for orderbookers brand wise in currency value.
2. I want to make adjustments (calibration in their monthly targets) with ratio mentioned in table named as TGTCALIB.

Note: Targets are made from their previous sales history (which is available in two tables acutalsales1 and actualsales2) by multiplying and increased number let suppose 110% of sales history of last4months, I did it successfully. Butt, since every orderbooker has different circumstances , therefore, I need to calibrate their targets with different %age which is mentioned in TGTCALIB table.

Please share formula in DAX for two way lookup value so that i become able to multiply targets to ratios in TGTCALIB table.

I can share file if there is no compulsion of sharing file link here.

Saturday, September 9, 2017 11:32 AM

• Dilawer,

First, let me be up front... I am no expert!

If you have access to YouTube, there are lots of Videos on how to do this.  There are several "set-up" steps, which are required to make this possible.  As I recall, the steps would be something along these lines:

1. In YouTube, search for "Power BI What If" or "Power BI Scenarios".  I know for a fact "Enterprise DNA" (<cite>https://www.youtube.com/channel/UCy2rBgj4M1tzK-urTZ28zcA) has covered this topic as well as Avi Singh. (PowerPivotPro) https://www.youtube.com/channel/UCRNmSv7mAPYiC0Y40TJijAw.  Search YouTube for and you will get the exact formulas for your measures.</cite>
2. Next, generally speaking, you will need to do the following:
3. Add a table with all the "what-if" scenarios.  In this case, .0 to .5 stepping by .05  (.00, .05, .10, .15 etc.)  Call this table "PCT Goals" or "PCT Targets".  Just a one column table.
4. Next you would add this as a slicer to your report.
5. Next, all your measures in your data model subject to this "markup" need to be iterator functions, such as Sales Target = SUMX([Table], Table[Column] * (1 + Selected Markup from Slicer))
6. You can then use the "Target.." measures in your reports as any other measure.

Here's a sample search:

Hope this get's you started...

John Thomas

• Edited by Thursday, March 1, 2018 5:57 PM
• Proposed as answer by Wednesday, January 15, 2020 5:21 PM
• Marked as answer by Monday, May 25, 2020 7:37 AM
Thursday, March 1, 2018 5:54 PM

### All replies

• Hi Dilawer,

It would be better if you can share the sample file and the expected result.

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Monday, September 11, 2017 6:27 AM
• Hi Dilawer,

It would be better if you can share the sample file and the expected result.

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Hi Dilawer,

Do you still need help with this? If so, can you kindly illustrate this scenario with some test data as requested by Willson? Ideally, you'd also provide the expected outputs/outcome based on the test data so that any proposed solutions can be verified against this.

Regards,

Thursday, February 22, 2018 7:58 AM
• Dilawer,

First, let me be up front... I am no expert!

If you have access to YouTube, there are lots of Videos on how to do this.  There are several "set-up" steps, which are required to make this possible.  As I recall, the steps would be something along these lines:

1. In YouTube, search for "Power BI What If" or "Power BI Scenarios".  I know for a fact "Enterprise DNA" (<cite>https://www.youtube.com/channel/UCy2rBgj4M1tzK-urTZ28zcA) has covered this topic as well as Avi Singh. (PowerPivotPro) https://www.youtube.com/channel/UCRNmSv7mAPYiC0Y40TJijAw.  Search YouTube for and you will get the exact formulas for your measures.</cite>
2. Next, generally speaking, you will need to do the following:
3. Add a table with all the "what-if" scenarios.  In this case, .0 to .5 stepping by .05  (.00, .05, .10, .15 etc.)  Call this table "PCT Goals" or "PCT Targets".  Just a one column table.
4. Next you would add this as a slicer to your report.
5. Next, all your measures in your data model subject to this "markup" need to be iterator functions, such as Sales Target = SUMX([Table], Table[Column] * (1 + Selected Markup from Slicer))
6. You can then use the "Target.." measures in your reports as any other measure.

Here's a sample search:

Hope this get's you started...

John Thomas

• Edited by Thursday, March 1, 2018 5:57 PM
• Proposed as answer by Wednesday, January 15, 2020 5:21 PM
• Marked as answer by Monday, May 25, 2020 7:37 AM
Thursday, March 1, 2018 5:54 PM