cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
viferenc
Helper I
Helper I

SQL query to DAX or to power query

Dear All,

 

I would like to ask for your help.

 

I have in my Power BI Desktop two table (tankolas, jaratok). (they are realted to trips and consumptions) 

 

From this two table I would like to create a new table, whitch special criterieas.

 

In Mysql the query is working but I´m not fit to convert it to DAX or to Power Query.

 

Is there any possibility if somebody could help me to convert it:

 

drop table if exists `liter_per_km_table`
;
create table `liter_per_km_table` (
`jaratszam` varchar(255) default null,
`gfrsz` varchar(255) default null,
`kml` double default null,
`kmh` double default null,
`liter` double default null,
`kmdiff` double default null,
`lpkm` double default null,
key `idx_gfrsz_kml_kmh` (`gfrsz`,`kml`,`kmh`)
) engine=innodb default charset=utf8
;
insert into liter_per_km_table
(select
t.JARATSZAM as jaratszam,
t.gfrsz as gfrsz,
tprev.`KM_ALLAS` as kml,
t.km_ALLAS as kmh,
t.liter as liter,
(
t.km_allas - ifnull(tprev.km_allas, 0)
) as kmdiff,
(
t.liter / (
t.KM_ALLAS - ifnull(tprev.km_allas, 0)
)
) as lpkm
from
(select
t.*,
(select
t2.`KM_ALLAS`
from
tankolas t2
where t2.`GFRSZ` = t.`GFRSZ`
and t2.`KM_ALLAS` < t.`KM_ALLAS`
and t2.JOGCIM <> 'Adblue'
order by t2.`KM_ALLAS` desc
limit 1) as prev_KM_ALLAS
from
tankolas t
where t.`JOGCIM` <> 'Adblue') t
left join tankolas tprev
on tprev.GFRSZ = t.GFRSZ
and tprev.`KM_ALLAS` = t.prev_KM_ALLAS and tprev.JOGCIM <>'Adblue'
where t.`JOGCIM` <> 'Adblue')
;

--
-- Query
--

select
r.jaratszam,
r.gfrsz,
r.ind_km,
r.erk_km,
lpkm_mx.kml,
lpkm_mx.kmh,
lpkm_mx.liter,
lpkm_mx.kmdiff,
round(lpkm_mx.lpkm, 3) as lpkm,
greatest(0, least(r.erk_km, lpkm_mx.kmh) - greatest(r.ind_km, lpkm_mx.kml)) as used_km,
round(greatest(0, least(r.erk_km, lpkm_mx.kmh) - greatest(r.ind_km, lpkm_mx.kml)) * lpkm_mx.lpkm, 3) as used_liter
from
jaratok r
inner join liter_per_km_table lpkm_mx
on r.`erk_km` > lpkm_mx.`kml`
and r.`ind_km` < lpkm_mx.`kmh`
and r.gfrsz = lpkm_mx.gfrsz
order by r.gfrsz, r.`IND_KM`
;

 

 

 

Thank you for your help.

 

Best regards

 

Feri

 

 

1 REPLY 1
Greg_Deckler
Super User
Super User

@viferenc Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors