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.

     

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

I received a value for 3/11 MachineId. Don't understnad why it wouldnt return the other ones.

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

measure is automatically filtering for those rows only which are in filter context with non blank values.

 

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(all('Table'),'Table'[Domain]=domain && 'Table'[Date]=max_date__))
 
i have modified return section and added  "all", this will return all table rows.
 
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

    Got this error now.
    A single value for column 'TagId' in table 'Events' cannot be determined....

    I do not have any filters active in my filter fields.

     

    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(ALL('Events');'Events'[MachineId]=domain && 'Events'[EventRaised]=max_date__)))

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi lukasjar 

       

      you have not closed bracket after tagid in return section.

      Calculate(max(table[tagid]),filter())

       

      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

        That solved it not giving an error, but still dosnt return anything =(