cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
leonardorm Visitor
Visitor

How to return the value of a other higher-valued column?

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 )

image.pngIteracao

 

trabalho.csv (http://dontpad.com/leonardorm/powerbi/trabalho)

image.pngTrabalho

 

trabalhofoto.csv (http://dontpad.com/leonardorm/powerbi/trabalhofoto)

image.pngTrabalhoFoto (daily snapshot)

 

image.pngExpected result

1 ACCEPTED SOLUTION

Accepted Solutions
TeigeGao Member
Member

Re: How to return the value of a other higher-valued column?

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:

2132.png

Best Regards,

Teige

1 REPLY 1
TeigeGao Member
Member

Re: How to return the value of a other higher-valued column?

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:

2132.png

Best Regards,

Teige