cancel
Showing results for
Did you mean:
Highlighted
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

Trabalho

TrabalhoFoto (daily snapshot)

Expected result

1 ACCEPTED SOLUTION

Accepted Solutions
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:

Best Regards,

Teige

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:

Best Regards,

Teige