Forum Discussion

yellingdog's avatar
yellingdog
New Member
4 years ago
Solved

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

3 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    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's avatar
      yellingdog
      New Member
      4 years ago

      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

       

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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