topic Re: Need help: Dax function to calculate a single value with given filters in DAX Commands and Tips

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

Anonymous — Mon, 14 Jun 2021 05:40:34 GMT

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?

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

Jihwan_Kim — Mon, 14 Jun 2021 06:12:41 GMT

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

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

Anonymous — Mon, 14 Jun 2021 06:36:57 GMT

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.

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

Jihwan_Kim — Mon, 14 Jun 2021 06:44:55 GMT

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

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

Anonymous — Mon, 14 Jun 2021 06:54:53 GMT

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