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 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!

3 Replies

  • 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