Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Matty
Helper II
Helper II

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

1 ACCEPTED SOLUTION

@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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

6 REPLIES 6
smpa01
Super User
Super User

Thanks @AlexisOlson 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
smpa01
Super User
Super User

@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.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

@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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Thanks for this.  I have used a similar construct before, but I'd forgotten it could be used for what I needed here.  I've actually now included a SUMMARIZE within the inside ADDCOLUMNS to only retain the columns I needed from the reference table.

Thanks again!

Matty

This looks about right but there's a missing comma before "Sales_Plan_Ratio".

ADDCOLUMNS (
    ADDCOLUMNS (
        Source,
        "Total_Sales_Plan",
            CALCULATE (
                SUM ( Source[Sales_Plan] ),
                ALLEXCEPT (
                    Source,
                    Source[Source_Plant],
                    Source[Material],
                    Source[Calendar_Week_Year]
                )
            )
    ), /* <--- Comma goes here. */
    "Sales_Plan_Ratio", COALESCE ( DIVIDE ( Source[Sales_Plan], [Total_Sales_Plan] ), 0 )
)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.