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

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

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

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