Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
Thanks @AlexisOlson
@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.
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
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 )
)
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |