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 have 3 tables: iteracao; trabalho; trabalhofoto (log: snapshot (daily)).
I need the sum of the "esforço" values of the largest data in the "trabalhofoto" table of data in the "trabalho" table where the data must be less than or equal to the final data ("datefim") of the work. "iteracao".
Using the sql below I get the result I need (last image), but how do I do this using DAX?
SELECT *, (SELECT SUM((SELECT tf.esforco FROM trabalhofoto tf WHERE t.trabalhoId = tf.trabalhoId AND tf.data <= i.dataFim ORDER BY tf.data DESC LIMIT 1)) as esforcoTotal FROM trabalho t) esforcoTotal FROM iteracao i
iteracao.csv ( http://dontpad.com/leonardorm/powerbi/iteracao )
trabalho.csv (http://dontpad.com/leonardorm/powerbi/trabalho)
trabalhofoto.csv (http://dontpad.com/leonardorm/powerbi/trabalhofoto)
Solved! Go to Solution.
Hi leonardorm,
According to your description, my understanding is that you want to calculate the sum of esforco whose data is max in each trabahoId group and less than the dataFim.
In this scenario, we can use the following DAX query:
Measure = SUMX ( SUMMARIZE ( trabalhofoto, trabalho[trabalhoId] ), CALCULATE ( MAXX ( TOPN ( 1, FILTER ( trabalhofoto, trabalhofoto[data] <= MIN ( iteracao[dataFim] ) ), trabalhofoto[data], DESC ), trabalhofoto[esforco] ) ) )
The result will like below:
Best Regards,
Teige
Hi leonardorm,
According to your description, my understanding is that you want to calculate the sum of esforco whose data is max in each trabahoId group and less than the dataFim.
In this scenario, we can use the following DAX query:
Measure = SUMX ( SUMMARIZE ( trabalhofoto, trabalho[trabalhoId] ), CALCULATE ( MAXX ( TOPN ( 1, FILTER ( trabalhofoto, trabalhofoto[data] <= MIN ( iteracao[dataFim] ) ), trabalhofoto[data], DESC ), trabalhofoto[esforco] ) ) )
The result will like below:
Best Regards,
Teige
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |