Forum Discussion

adin's avatar
adin
Helper I
5 years ago
Solved

Variable Virtual Table column reference

Hi guys,

 

I need to combine select column from two tables and then run a calc over the top of the union of these two tables but am having trouble pulling out the reference columns and running the calc. For some reason, it doesn't want to recognise the column names from the variables above. 

 

below is the code I'm using 

 

Combined Weighted Avg LVR = 

var c_pipelinetable = SELECTCOLUMNS(FILTER('DB Pipeline','DB Pipeline'[Pr] = "xxx"),
                                     "c_Account", 'DB Pipeline'[Application ID],
                                     "c_LVR_by_Loan_Amount", 'DB Pipeline'[LVR by Loan Amount],
                                     "c_Loan_Amount", 'DB Pipeline'[Loan Amount])

var c_settledtable = SELECTCOLUMNS(FILTER('Dataset - Development Environment',CONTAINSSTRING('Dataset - Development Environment'[Product_Description],"xxx")),
                                    "c_Account", 'Dataset SPV - Development Environment'[Account_Number],
                                    "c_LVR_by_Loan_Amount", 'Dataset SPV - Development Environment'[LVR by Loan Amount],
                                    "c_Loan_Amount", 'Dataset SPV - Development Environment'[Principal_Balanace])

var _PipelineSettled_table = UNION(c_pipelinetable,c_settledtable)

return

CALCULATE(sum(c_Loan_Amount))

 

  • Hi adin ,

     

    According to your dax formula, the reason for the error is that the parameter of the sum function comes from a specific column in the data source, and "c_Loan_Amount" in the above expression is a virtual column created. Therefore an error is returned.

    refer to official document: SUM function (DAX) - DAX | Microsoft Docs

     


    If it still doesn't solve your problem, can you provide test data (delete sensitive information), because I can't do further tests with the formula alone. Looking forward to your reply.

    Best Regards,
    Henry

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • The error I'm getting is "the SUM function only accepts a column reference as an argument."

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support

    Hi adin ,

     

    According to your dax formula, the reason for the error is that the parameter of the sum function comes from a specific column in the data source, and "c_Loan_Amount" in the above expression is a virtual column created. Therefore an error is returned.

    refer to official document: SUM function (DAX) - DAX | Microsoft Docs

     


    If it still doesn't solve your problem, can you provide test data (delete sensitive information), because I can't do further tests with the formula alone. Looking forward to your reply.

    Best Regards,
    Henry

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.