cancel
Showing results for
Did you mean:
Highlighted
Post Prodigy

## How to write a complex lookup dax?

I need to write a dax which can lookup a value in 1 table and pick up the units related to that value and sum that up and then multiply that value to the same value unit in another table?

For example I have 2 tables A and B, now I want to right the dax in a way so that I can lookup field A1 in table A and pick up all the units related to that fields and Sum them up, and then lookup the same value A1 in table B and (its units are already summed up so no need to do that) multiply the units of table A into table B values units?

Is that possible? Any example can help me understand the process?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III

## Re: How to write a complex lookup dax?

Hi @Anmolgan ,

Try the following code.

```Measure =
VAR SALES_CONDITION =
MAX ( Sales[Condition] )
RETURN
SUMX (
Sales;
SUM ( Sales[Unit] )
* CALCULATE (
MAXX ( COPA; COPA[Unit] );
FILTER ( ALL ( COPA[Valid Date] ); COPA[Valid Date] <= MAX ( Sales[Date] ) );
COPA[Condition] = SALES_CONDITION
)
)```

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog:

Power BI em Português

5 REPLIES 5
Highlighted
Solution Sage

## Re: How to write a complex lookup dax?

Probably a way to do that in DAX, but I'd have thought this'd be something achieved much easier using Power Query to summarise the data?

Highlighted
Post Prodigy

## Re: How to write a complex lookup dax?

thank you for your comment, are there any examples that I can use to achive this kind of behaviour?

Highlighted
Super User III

## Re: How to write a complex lookup dax?

Hi @Anmolgan ,

Can you share a sample data and expected result?

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog:

Power BI em Português

Highlighted
Post Prodigy

## Re: How to write a complex lookup dax?

Let me give you the sample query so there are two conditions that I would like to full fill in this case Below are my tables:

Sales Data:

Conditon     Unit   Date

Z130             500   15/5/2019

Z130             200   16/5/2019

Z140             400    20/5/2019

COPA Calculations

Conditon      Unit    Valid Date

Z130            100     04/04/2019

Z140            200     04/04/2019

I need to pick up respective condition from Sales Data like for Z130 it should pick up Z130 Volumes and it should lie between Valid Date also so 2 conditions, if true then pick up all the volumes sum it and multiply it by the condtion in COPA Calculation with the Unit and this needs to be done for each condition and should be in 1 dax.

Do let me know if more elaboration is required

Highlighted
Super User III

## Re: How to write a complex lookup dax?

Hi @Anmolgan ,

Try the following code.

```Measure =
VAR SALES_CONDITION =
MAX ( Sales[Condition] )
RETURN
SUMX (
Sales;
SUM ( Sales[Unit] )
* CALCULATE (
MAXX ( COPA; COPA[Unit] );
FILTER ( ALL ( COPA[Valid Date] ); COPA[Valid Date] <= MAX ( Sales[Date] ) );
COPA[Condition] = SALES_CONDITION
)
)```

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog:

Power BI em Português

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors