How to get a value filtered by another column in the same table, with DAX

[marloswn]

Hello everyone! I'm tryng to select a value based on another column of the same table. In this table, I have a lot of user's IDs, and I have different weights and dates for some users. In this case, I want to return the weight related with the min date, for each user.

For example:

user_id	weight	date
1	80.5	01/06/2020
1	81.3	10/09/2020
1	80	20/12/2020

Based on this table, I'd like to return just the value '80.5', because for this user, this is the weight with the minimum date.

Please, could anyone help me make this happen with DAX?

Thanks in advance!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-alq-msft]

Hi, @marloswn 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure as below.

Result = 
var mindate = 
CALCULATE(
    MIN('Table'[Date]),
    ALLEXCEPT('Table','Table'[User_id])
)
return
CALCULATE(
    SUM('Table'[Weight]),
    FILTER(
        ALLEXCEPT('Table','Table'[User_id]),
        [Date]=mindate
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[marloswn]

Thanks @v-alq-msft , but, in this case, I want just de 'weight' column, not user_id. And, as I mentioned, the logic is breaking when it finds an duplicate data. Is there any solution for this?

[v-alq-msft]

Hi, @marloswn 

You may try the following measure. The pbix file is attached in the end.

Measure = 
CALCULATE(
    SUM('Table'[Weight]),
    FILTER(
        ALL('Table'),
        [Date]=
        CALCULATE(
            MIN('Table'[Date]),
            ALL('Table')
        )
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[marloswn]

Guys, I almost managed to reach a solution:

FirstWeight =

VAR SearchValue = MIN(Date)

RETURN

CALCULATE(

SELECTEDVALUE(table[Weight], 999),

FILTER(

ALLNOBLANKROW(table[date]), table[date] == SearchValue),

ALL(table[Weight])

)

This is returning 999 when there are two equal dates.

Could anyone please help me to fix this issue?

Tks!