Forum Discussion

lukasjar's avatar
lukasjar
Icon for Resolver I rankResolver I
6 years ago
Solved

Value from max date

Hello Community. I am trying to retrieve the TagId at Max EventRaised for each MachineID in a Direct Query.   From the picture below. 31207 = Press.31207.Störning1Utast1 Kit_Pack1 = KITLinan.Pac...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    share your pbix.

     

    and where are you adding this query

     

    select A.* from dbo.events a
    left join (select MachineID,max(EventRaised) _EVENT from dbo.[events]
    Group by MachineID) b ON A.MachineID=B.MachineID AND a.EventRaised=B._EVENT
    WHERE b._EVENT IS NOT NULL

     

    Get Data->Select Sql server -> ENter server name  & database name->DirectQuery Mode-> Click on advance -> paste this query there ->

    And load your data.

     

    this will give you direct result set 

     

    Thanks & regards,
    Pravin Wattamwar
    www.linkedin.com/in/pravin-p-wattamwar

    If I resolve your problem Mark it as a solution and give kudos.

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Hi lukasjar 
Create calculated column as below
Column =

var max_date__=calculate(max('Table'[EventRaised]),ALLEXCEPT('Table','Table'[MachineID]))
return
LOOKUPVALUE('Table'[TagID],'Table'[EventRaised],max_date__)
 
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

 

lukasjar's avatar
lukasjar
Icon for Resolver I rankResolver I
6 years ago
 

Can't use columns in direct query.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Hi lukasjar  Try measure
    Measure 3 =
    var domain=SELECTEDVALUE('Table'[MachineID])
    var max_date__=calculate(max('Table'[date]),FILTER(ALL('Table'),'Table'[MachineID]=domain))
    return
    CALCULATE(MAX('Table'[Status]),FILTER('Table','Table'[MachineID]=domain && 'Table'[Date]=max_date__))
     
    replace column names with your column names
     
    Thanks & regards,
    Pravin Wattamwar
    www.linkedin.com/in/pravin-p-wattamwar

    If I resolve your problem Mark it as a solution and give kudos.
     
     

     

    • lukasjar's avatar
      lukasjar
      Icon for Resolver I rankResolver I
      6 years ago

      Unfortuantely still returns blank.

      This is what I wrote.

       

      Measure 3 =
      var domain=SELECTEDVALUE('Events'[MachineID])
      var max_date__=calculate(max('Events'[EventRaised]);FILTER(ALL('Events');'Events'[MachineID]=domain))
      return
      CALCULATE(MAX('Events'[TagId]);FILTER('Events';'Events'[MachineID]=domain && 'Events'[EventRaised]=max_date__))

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi lukasjar 

         

        Same thing working for me.

        Column =
        var max_date__=calculate(max('Table'[date]),ALLEXCEPT('Table','Table'[Domain]))
        return
        LOOKUPVALUE('Table'[Status],'Table'[Date],max_date__)
         
        Measure 3 =
        var domain=SELECTEDVALUE('Table'[Domain])
        var max_date__=calculate(max('Table'[date]),FILTER(ALL('Table'),'Table'[Domain]=domain))
        return
        CALCULATE(MAX('Table'[Status]),FILTER('Table','Table'[Domain]=domain && 'Table'[Date]=max_date__))

         

         

         

        are you sure you have not added any filters on visual/Page?

        Still not working then you can give try to measures suggested by Mariusz  & amitchandak 

         

        Thanks & regards,
        Pravin Wattamwar
        www.linkedin.com/in/pravin-p-wattamwar

        If I resolve your problem Mark it as a solution and give kudos.