Convert DAX Expressions to SQL

[Anonymous]

I'm a little new to DAX and have a problem!

I've been tasked with converting several existing DAX expressions to SQL Server, due to the fact that there are external customers who do not have access to our existing Power BI resources. 

I've managed to convert all of the expessions, except for two. These are:

Completed Revenue Flight Controllable D0 Success =

    CALCULATE(SUM('FlightSchedulePerformanceDetail'[Controllable D0])

                ,FILTER('FlightSchedulePerformanceDetail'

                        ,'FlightSchedulePerformanceDetail'[Accountable Flight] = 1 &&

                        'FlightSchedulePerformanceDetail'[Is In] = 1))

Controllable D0 =

IF('FlightSchedulePerformanceDetail'[Is Out] = 0,0,IF(AND('FlightSchedulePerformanceDetail'[Total Departure Delay Minutes]>0,

('FlightSchedulePerformanceDetail'[Dept Delay 1 Controllable]+

'FlightSchedulePerformanceDetail'[Dept Delay 2 Controllable]+

'FlightSchedulePerformanceDetail'[Dept Delay 3 Controllable]+

'FlightSchedulePerformanceDetail'[Dept Delay 4 Controllable])>1),0,1))

If someone could provide some insight into these, that would be fantastic!

Thank you!

[vivran22]

Hello @Anonymous 

You may try following:

For Completed Revenue Flight Controllable D0 Success


SELECT SUM([Controllable D0])
FROM 'FlightSchedulePerformanceDetail'
 WHERE 'FlightSchedulePerformanceDetail'[Accountable Flight] = 1 
 AND 'FlightSchedulePerformanceDetail'[Is In] = 1

For Controllable D0 

CASE WHEN 'FlightSchedulePerformanceDetail'[Is Out] = 0 THEN 0
    WHEN 'FlightSchedulePerformanceDetail'[Total Departure Delay Minutes] > 0 AND 
	('FlightSchedulePerformanceDetail'[Dept Delay 1 Controllable] + 
	'FlightSchedulePerformanceDetail'[Dept Delay 2 Controllable] + 
	'FlightSchedulePerformanceDetail'[Dept Delay 3 Controllable] + 
	'FlightSchedulePerformanceDetail'[Dept Delay 4 Controllable] ) > 1
        ) THEN 0
        ELSE 1
   END

You might have to tweak it, but I am hopeful it will give you some direction.

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂 (Hit the thumbs up button!)
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)


https://www.vivran.in/

Connect on LinkedIn

[Anonymous]

Thank  you! This does the trick...almost!

For the Controllable D0 field, I actually need to wrap the entire thing into a SUM. I've been trying and can't quite get it just right.

Any ideas??

[vivran22]

@Anonymous 

Ideally this should do the trick:

SELECT 
SUM(
CASE WHEN 'FlightSchedulePerformanceDetail'[Is Out] = 0 THEN 0
    WHEN 'FlightSchedulePerformanceDetail'[Total Departure Delay Minutes] > 0 AND 
	('FlightSchedulePerformanceDetail'[Dept Delay 1 Controllable] + 
	'FlightSchedulePerformanceDetail'[Dept Delay 2 Controllable] + 
	'FlightSchedulePerformanceDetail'[Dept Delay 3 Controllable] + 
	'FlightSchedulePerformanceDetail'[Dept Delay 4 Controllable] ) > 1
        ) THEN 0
        ELSE 1
   END
) as SumTotal

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂 (Hit the thumbs up button!)
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)


https://www.vivran.in/

Connect on LinkedIn