Forum Discussion

Anmolgan's avatar
Anmolgan
Icon for Post Prodigy rankPost Prodigy
7 years ago
Solved

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?

Need Help
Show and Tell
  • MFelix's avatar
    MFelix
    7 years ago

    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

     

5 Replies

  • jthomson's avatar
    jthomson
    Icon for Solution Sage rankSolution Sage
    7 years ago

    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's avatar
      Anmolgan
      Icon for Post Prodigy rankPost Prodigy
      7 years ago

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

    • Anmolgan's avatar
      Anmolgan
      Icon for Post Prodigy rankPost Prodigy
      7 years ago

      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's avatar
        MFelix
        Icon for Super User rankSuper User
        7 years ago

        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