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 

 

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.