CONTAINSTRING in Measure problem

[yellingdog]

Hello, 

I have created a gauge that shows the latest value of a measurement. I do this with this measure:

Temperature = CALCULATE( SELECTEDVALUE(koreaData[temp]), FILTER(koreaData, koreaData[timestamp] = MAX(koreaData[timestamp])))

Timestamp is a string.

However there is a single measurement that I don't want to use in this. So i tried this Measure but it doesn't work:

Temperature = CALCULATE( SELECTEDVALUE(koreaData[temp]), FILTER(koreaData, koreaData[timestamp] = MAX(koreaData[timestamp]) && not(CONTAINSSTRING(koreaData[timestamp], "21/09/2021"))))

The gauge then shows the message BLANK.

Is there a way to do this with EventProcessedUtcTime instead?

any tips?

thanks

[VahidDM]

Hi @yellingdog 

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

[yellingdog]

So yeah sorry about that.

Columns temp and timestamp are 1:1.

An example of temp is 22.2

and an example of timestamp is 11/11/2021 13:05:23

22.2	11/11/2021 13:05:23

Temperature is my Measure and it is visualized in a gauge.

I would like a single timestamp value (21/06/2021 12:05:22) to not be included in the Measure.

hope it's better now

thanks

[Anonymous · Accepted solution]

Hi @yellingdog ,

First, please change the data type of column timestamp as Date/Time in Power Query Editor:

Change the data type of column timestamp as Date/Time

Then update your measure as below:

Temperature =

VAR _excludedate = DATE ( 2021, 9, 21 )

RETURN

CALCULATE (

SELECTEDVALUE ( koreaData[temp] ),

FILTER (

koreaData,

koreaData[timestamp] = MAX ( koreaData[timestamp] )

&& DATE ( YEAR ( 'koreaData'[timestamp] ), MONTH ( 'koreaData'[timestamp] ), DAY ( 'koreaData'[timestamp] ) ) <> _excludedate

)

)

You can find the attachment for all details. 

Best Regards