Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Latest reading formula

Hi, 

Can someone help me with a calculated column and a dax formula, both would have been nice. I want only the latest reading of every ID if the the tempratur is 5 or above give me true, if not give me false. 

I am only intrested in the latest reading for that paticular day. 




Here are a sample from the data. 

thanx in advance



Need Help

7 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi Anonymous 

    Please try the below Measure.

     

    Measure = 
VAR __tbl = ADDCOLUMNS( 
    VALUES( 'YorTable'[Id] ),
    "mDate", CALCULATE( 
        MAX('YorTable'[Timestamp] ), 
        ALLEXCEPT( 'YorTable', 'YorTable'[Id] )
    )
)
RETURN 
CALCULATE(
    COUNTROWS( 'YorTable' ),
    KEEPFILTERS( 
        TREATAS( __tbl, 'YorTable'[Id], 'YorTable'[Timestamp] ) 
    )
) >= 5

     

    Regards,
    Mariusz

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Mariusz , 

      Thanx for your help but, 

      I am only getting false, not getting true or false? Can you please check and modify this. Something is clearly missing. 


      • Mariusz's avatar
        Mariusz
        Community Champion
        7 years ago

        Hi Anonymous 

        The expression I posted previously was a Measure if you need a column, please use the below

         

        Spoiler
        Column = 
VAR mDate = CALCULATE(
    MAX(YourTable[Timestamp]),
    ALLEXCEPT(YourTable, YourTable[Id])
)
RETURN YourTable[Timestamp] = mDate && YourTable[Temoratur] >= 5

        Regards,
        Mariusz

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

    • Mariusz's avatar
      Mariusz
      Community Champion
      7 years ago

      Hi Anonymous 

       

      Samall adjustment to a Measure

      Measure = 
VAR __tbl = ADDCOLUMNS( 
    VALUES( 'YourTable'[Id] ),
    "mDate", CALCULATE( 
        MAX('YourTable'[Timestamp] ), 
        ALLEXCEPT( YourTable, YourTable[Id] )
    )
)
RETURN 
CALCULATE(
    MAX( 'YourTable'[Temoratur] ),
    KEEPFILTERS( 
        TREATAS( __tbl, 'YourTable'[Id], 'YourTable'[Timestamp] ) 
    )
) >= 5

      Regards,
      Mariusz

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi again Mariusz 

        Thanx alot for all help this looks better  I will test this out very soon. I am drillingthrough from one page to the next. So when I am on the next page I want this value to be visualise this in a kpi card in my report, if the value is 5 or above give me green light, if 5 or below give me red. How can I visualise this in a best possible way?

        Can I use the adcanve card, or any help would be very nice. I am open for any solution and visualtiation. Giving you a screenshot with a example white circle.