Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em Português
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
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êsProbably 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?
thank you for your comment, are there any examples that I can use to achive this kind of behaviour?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |