cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
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
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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

5 REPLIES 5
Highlighted
Solution Sage
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
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
Super User III

Re: How to write a complex lookup dax?

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Post Prodigy
Post Prodigy

Re: How to write a complex lookup dax?

@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

Highlighted
Super User III
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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

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

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

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

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors