Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
yellingdog
New Member

CONTAINSTRING in Measure problem

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

1 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/TimeChange 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
)
)

yingyinr_0-1636962891359.png

You can find the attachment for all details. 

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
VahidDM
Super User
Super User

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/

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.211/11/2021 13:05:23

 

yellingdog_0-1636622464289.png

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

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/TimeChange 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
)
)

yingyinr_0-1636962891359.png

You can find the attachment for all details. 

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.