cancel
Showing results for
Did you mean:
Helper I

## Need help: Dax function to calculate a single value with given filters

Hello!

I have a seemingly very simple problem but I just can not come up with the solution.

I have the following table. It contains example warnings, a date and a value to the respective date:

 TestWarning Date Value Test Value 1 25.01.2021 5 Test Value 1 01.02.2021 11 Test Value 1 08.02.2021 7 Test Value 1 15.02.2021 6 Test Value 2 25.01.2021 12 Test Value 2 01.02.2021 7 Test Value 2 08.02.2021 5 Test Value 2 15.02.2021 5 Test Value 3 25.01.2021 0 Test Value 3 01.02.2021 2 Test Value 3 08.02.2021 4 Test Value 3 15.02.2021 2

Now in Power BI I need one measure per example warning that returns the value for the latest date.
For "Test value 1" the value I am looking for would be "6" for 15.02.2021, for "Test value 2" the value I am looking for would be "5" for 15.02.2021 and for "Test value 3" the value I am looking for would be "2" for 15.02.2021.

To calculate this value for "Test value 1", for example, I thought of something like the following measure.

Latest value 1 =
VAR _value =
CALCULATE (
FIRSTNONBLANK ( 'Data Warning Test'[Value], TRUE ),
FILTER (
'Data Warning Test', 'Data Warning Test'[TestWarning] = "Test Value 1"
),
FILTER ('Data Warning Test', 'Data Warning Test'[Date] = LASTDATE ( 'Data Warning Test'[Date] )
)
)
RETURN
IF ( ISBLANK ( _value )"No data"_value )

But the problem is that I don't know which DAX function to use inside the CALCULATE function to search for this single value. The FIRSTNONBLANK function here in the example doesn't work, nor does VALUES or MAXX, which I came across while researching. Does anyone have any ideas on how I can write the Measure?

1 ACCEPTED SOLUTION
Community Champion

Hi, @JMPowerBI

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

4 REPLIES 4
Community Champion

Hi, @JMPowerBI

Please check the below picture and the sample pbix file's link down below.

Latest Date Value Measure =
IF (
ISFILTERED ( Data[TestWarning] ),
SUMX ( FILTER ( Data, Data[Date] = MAX ( Data[Date] ) ), Data[Value] )
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Helper I

Hello @Jihwan_Kim

The values in your table are correct! The problem is that I need individual measures for each value (One measure for "Test Value 1", another for "Test Value 2" and a third for "Test Value 3"). The reason is that I need to pin these three visuals on a dashboard later on and set up data warnings. That is why I need three individual measures for three card visuals instead of one table with all of the values combined.

Community Champion

Hi, @JMPowerBI

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Helper I

Hello @Jihwan_Kim !

Thank you so much for your super fast solution!

It works perfectly and it is exactly what I was looking for!

Thank you so much!

Kind regards

JMPowerBI

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.