Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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? 

  • Hi, Anonymous 

    Thank you for your feedback.

    Please check the link down below.

     

     

    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

4 Replies

  • Hi, Anonymous 

    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] )
    )

     

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable

      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.

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User

        Hi, Anonymous 

        Thank you for your feedback.

        Please check the link down below.

         

         

        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