translate import logic into direct query

[Anonymous]

Hi guys,

in direct query an sql server is giving me a table with time stamps and task names.

i now need to subtract these time stamps from each other.

See sample data: 

1) Created unique ID in the correct order  (measure)

2) Created unique ID VGL 1 (measure)

Now i need a formula for the following logic:
Zeitstempel for ID - Zeitstempel for ID VGL 1

3) Logic before direct query mode

In Import mode i duplicated my source table, did the steps 1) and 2) (calculated column) and linked them by ID 1:1 ID VGL 1.

This method isnt working bc i cant link measures.

I dont know what to do know..

Would appreciate some help.

Kind regards

Philipp

[v-zhenbw-msft]

Hi @Anonymous ,

What is the logic of your Measure ID1 and ID2?

Maybe you can refer the pbix that attached.

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data?

It will be helpful if you can show us the exact expected result based on the tables. Please upload your files to OneDrive For Business and share the link here.

Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

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

[amitchandak]

@Anonymous , try like 

measure =
datediff(max(Table[zetistempel]), calculate(max(Table[zetistempel]), filter(allselected(Table), Table[zetistempel] <max(Table[zetistempel]) && Table[ID] =max(Table[ID]))),second)

or 

measure =
datediff(max(Table[zetistempel]), calculate(max(Table[zetistempel]), filter(allselected(Table), Table[VGL] =max(Table[VGL])-1 && Table[ID] =max(Table[ID]))),second)

[Anonymous]

@amitchandak , yet it isnt working like it should.

again, the measure needs to subtract the 2 time stamps for ID.1 = ID.2.

e.g. Calculate Time Stamp for ID.1 = 50: 

1) measure needs to look up ID.1 = 50 in ID.2.

2) measure needs to subtract "Zeitstempel" for ID.1 = 50 and ID.2 = 50.

I cant find a solution to this, would really appreciate your help.

[amitchandak]

@Anonymous , you have to try a measure like

measure =
datediff(max(Table[zetistempel]), calculate(max(Table[zetistempel]), filter(allselected(Table), Table[zetistempel] <max(Table[zetistempel]))),second)

[Anonymous]

@amitchandak , thanks for the formula.

yet it doesnt contain the most important part in this logic:
I need to get the difference between the Time Stamps for ID = ID VGL 1.

maybe this visual makes it clearer.

Thank you!!