Power BI | DAX | Convert SQL to DAX

 Hi

If you are new to DAX, then this post will help you compare DAX with SQL and get an overview of DAX. It is assumed that the reader is aware of SQL (basics).

Consider the below tables that I have taken from Tableau's Sample Super Store dataset. You can download this data set from Tableau site or search Tableau Sample Super Store data set. It is an excel.

I would like to get all the orders from the city 'Los Angeles' and the people who have ordered them. From RDBMS querying perspective, my SQL would look something like this

SELECT  O.OrderID [City_OrderID]
	   ,O.OrderDate [City_OrderDate]
	   ,O.Sales [City_Slaes]
	   ,O.Profit [City_Profit]
	   ,O.Quantity [City_Quantity]
	   ,O.Region [City_Region]
	   ,P.Person [Person]
FROM Orders O
INNER JOIN People P ON O.Region = P.Region
WHERE City = 'Los Angeles'

How would you write a DAX query to get the same results as above from a Power BI data set? Below is the DAX

OrdersByCity = 
 SELECTCOLUMNS(FILTER(GENERATE(Orders,People),Orders[Region] 

                    = People[Region] && Orders[City] = "Los Angeles")
                               ,"OrderID",Orders[Order ID]
                               ,"OrderDate",Orders[Order Date]
                               ,"Sales",Orders[Sales]
                               ,"Profit",Orders[Profit]
                               ,"Quantity",Orders[Quantity]
                               ,"City_Region",Orders[Region]
                               ,"Person",People[Person])

Output from Power BI

GENERATE is a JOIN, FILTER is a WHERE clause, SELECTCOLUMNS is a SELECT clause

I have written this post to make a comparison between SQL and DAX querying. In case if you are new to DAX, like me, then it may help you in getting started, if you have little bit of SQL background.

-Yadnesh Madiwale