Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Convert DAX Expressions to SQL

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 acce...
vivran22's avatar
vivran22
Community Champion
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    vivran22
    Community Champion
    6 years ago

    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