How to write a complex lookup dax?

[Anmolgan]

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?

[MFelix]

Hi @Anmolgan ,

Can you share a sample data and expected result?

Please see this post regarding How to Get Your Question Answered Quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 (courtesy of @Greg_Deckler).

Regards,

MFelix

[Anmolgan]

@MFelix 

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

[MFelix · Accepted solution]

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

[jthomson]

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?

[Anmolgan]

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