Forum Discussion

lukasjar's avatar
lukasjar
Resolver 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
Resolver I
6 years ago

Unfortunatly still not working.

The first two are working on a imported query, not on direct query.

The third solution you suggested I get an error message, pic below.

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi lukasjar 

 

I have created your datset on sql server and created directQUery.

The above two measure are showing same result which i was getting using import.

DO one thing create new pbix file and check.

Measures are working fine with directquery and yah i need to look onto column issue.

 

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 

     

    Use below query while connecting to your datasource using directquery.

     

    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

     

    I have given you some measures in last post if you want your output using measures.

     

    There are some limitation while creating Calculated column when source is directquery.

    Hence calculate,Allexcept,filter and earlier are not working in previous formula.

    Check it out here

    https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-directquery-mode-ssas-2016?view=asallproducts-allversions

     

     

    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
    Resolver I
    6 years ago

    I do not understand why it is not working on my end then, but must be something wrong in what I am doing i guess.

    I have done a new pbix file with a new query as you wrote in the second message. Still have the same result. Will try sending you a movie of the pbix and measurments.

     

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.