Forum Discussion

Matty's avatar
Matty
Helper II
4 years ago
Solved

SQL to DAX Question...

Hi All,

What comes quite naturally to me in SQL can prove a bit more challenging to replicate in DAX based on my current knowledge level!

Let's take the following simple example:

SELECT
	DA.Source_Plant,
  Target_Plant,
  DA.Material,
  DA.Calendar_Week_Year,
  DA.Sales_Plan,
	SQ1.Total_Sales_Plan,
	ISNULL(DA.Sales_Plan / NULLIF(SQ1.Total_Sales_Plan, 0), 0) AS Sales_Plan_Ratio
FROM
	Data_Upload AS DA
	LEFT OUTER JOIN
		(
			SELECT
				Source_Plant,
				Material,
				Calendar_Week_Year,
				SUM(Sales_Plan) AS Total_Sales_Plan
			FROM
				Data_Upload
			GROUP BY
				Source_Plant,
				Material,
				Calendar_Week_Year
		) AS SQ1
	ON SQ1.Source_Plant = DA.Source_Plant
	AND SQ1.Material = DA.Material
	AND SQ1.Calendar_Week_Year = DA.Calendar_Week_Year

As you can see, we are selecting data from a table (Data_Upload) and then joining to the same table again via sub query (SQ1), but this time with the data grouped at a higher level.  Using this method the result is a table that shows me the lower grain result as well as the higher grain result, and I can then create calculated columns against the two grains (e.g. the column Sales_Plan_Ratio).

I'm fairly familiar with aggregators and iterators in DAX, as well as SUMMARIZECOLUMNS, etc., but I don't know the best approach to tackling the above.  Does any have any advice they're prepared to share, please?

Thanks,

Matty

Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    Matty  I think this would be direct transalation 

    Table = ADDCOLUMNS(ADDCOLUMNS(Source,"Total_Sales_Plan",CALCULATE(SUM(Source[Sales_Plan]),ALLEXCEPT(Source,Source[Source_Plant], Source[Material],Source[Calendar_Week_Year])))
"Sales_Plan_Ratio",coalesce(DIVIDE(Source[Sales_Plan],[Total_Sales_Plan]),0))

     

    if not, please provide a sample data and what is the output that TSQL generates

6 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    Matty  in SQL you can write this as a query to return a table.

    In DAX, you can write a query to return a derived table or utilize data model and write a measure to return what the above query would return.

     

    Can you please provide more context as to what is your intention as how do ou intend to achieve this? DAX derived table/Measure?

     

    If it is measure that you intend, can you please provide sample data and desired output else syntax by syntax translation fromSQL to DAx is also possible if you want to achieve this through a DAX derived table.

    • Matty's avatar
      Matty
      Helper II
      4 years ago

      Hi smpa01,

      I'm reasonably good at writing measures and creating calculated tables in DAX, but I've not had much luck finding anything that replicates what the SQL query above is doing.
      I'm aware of variables in DAX and I was thinking this kind of table could be built up via a couple of variables, which are then brought together to achieve the final result.  But I've not had much luck getting anything to work.

      Any ideas how the above would convert over to DAX in the form of a calculated table?

      Thanks,

      Matty

      • smpa01's avatar
        smpa01
        Community Champion
        4 years ago

        Matty  I think this would be direct transalation 

        Table = ADDCOLUMNS(ADDCOLUMNS(Source,"Total_Sales_Plan",CALCULATE(SUM(Source[Sales_Plan]),ALLEXCEPT(Source,Source[Source_Plant], Source[Material],Source[Calendar_Week_Year])))
"Sales_Plan_Ratio",coalesce(DIVIDE(Source[Sales_Plan],[Total_Sales_Plan]),0))

         

        if not, please provide a sample data and what is the output that TSQL generates