cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JMPowerBI
Helper I
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:

 

TestWarningDateValue
Test Value 125.01.20215
Test Value 101.02.202111
Test Value 108.02.20217
Test Value 115.02.20216
Test Value 225.01.202112
Test Value 201.02.20217
Test Value 208.02.20215
Test Value 215.02.20215
Test Value 325.01.20210
Test Value 301.02.20212
Test Value 308.02.20214
Test Value 315.02.20212

 

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

Hi, @JMPowerBI 

Thank you for your feedback.

Please check the link down below.

 

Picture1.png

 

https://www.dropbox.com/s/p42g4vqdben9mbs/jmpowerbi.pbix?dl=0 

 

 

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.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

4 REPLIES 4
Jihwan_Kim
Community Champion
Community Champion

Hi, @JMPowerBI 

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

 

Picture1.png

 

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

 

 

https://www.dropbox.com/s/p42g4vqdben9mbs/jmpowerbi.pbix?dl=0 

 

 

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.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Hello @Jihwan_Kim 

 

thank you for your quick reply!

 

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.

Hi, @JMPowerBI 

Thank you for your feedback.

Please check the link down below.

 

Picture1.png

 

https://www.dropbox.com/s/p42g4vqdben9mbs/jmpowerbi.pbix?dl=0 

 

 

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.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

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

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.