Forum Discussion

Lamarandteddy's avatar
Lamarandteddy
New Member
2 years ago
Solved

Multiple Columns Error

Hello all,

 

I am trying to show an output of all of the months that have a budget value of more than $444000 in 2023. However, I keep getting the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." Can someone please assist?

 

Here is my code:

 

AggregatedBudgetMonthsOver440K =
GROUPBY(
        FILTER(
            'FP&A Metrics',
            YEAR('FP&A Metrics'[Month]) = 2023 &&
            'FP&A Metrics'[Exercise Type] = "Budget"
        ),
        'FP&A Metrics'[Month],
        "TotalValue", SUMX(CURRENTGROUP(), 'FP&A Metrics'[Value]> 444000)
    )
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Lamarandteddy ,

     

    Thanks for the reply from lbendlin  Ashish_Mathur , please allow me to provide another insight.

     

    Please try:

    BudgetValuesOver100K = 
FILTER(
    SUMMARIZE(
        FILTER(
            'FP&A Metrics'.
            'FP&A Metrics'[Exercise Type] = "Budget"
        ),
        'FP&A Metrics'[Month].
        "TotalValue", SUM('FP&A Metrics'[Value])
    ),
[TotalValue] > 10000
)

     

    This is the original data table:

     

    This is the data table created by New Table, showing data for months where [TotalValue] > 10000.

     

    The data you provided has only one month and the Month type is not Date, please note this in your original data table.

     

    Please change [TotalValue] > 10000 to [TotalValue] > 444000.

     

    The pbix file is attached.

     

    If you have any other questions please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

15 Replies

    • Lamarandteddy's avatar
      Lamarandteddy
      New Member
      2 years ago

      I am trying to do perform a tabular funtion, not a scalar function Attached is what I am trying to succesfully perform. The other attached is what I get based on the error I listed.. 

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

        There is no such thing as a tabular function in DAX.  Your only option would be a calculated table but that is immutable.

         

        Table variables can be used inside a measure but the result must be a scalar.

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago
    I am trying to show an output of all of the months that have a budget value of more than $444000 in 2023.

    How are you planning to show that?  Measures can only return scalar values.  You would have to use your result as a filter inside a measure.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Lamarandteddy ,

     

    Thanks for the reply from lbendlin  Ashish_Mathur , please allow me to provide another insight.

     

    Please try:

    BudgetValuesOver100K = 
FILTER(
    SUMMARIZE(
        FILTER(
            'FP&A Metrics'.
            'FP&A Metrics'[Exercise Type] = "Budget"
        ),
        'FP&A Metrics'[Month].
        "TotalValue", SUM('FP&A Metrics'[Value])
    ),
[TotalValue] > 10000
)

     

    This is the original data table:

     

    This is the data table created by New Table, showing data for months where [TotalValue] > 10000.

     

    The data you provided has only one month and the Month type is not Date, please note this in your original data table.

     

    Please change [TotalValue] > 10000 to [TotalValue] > 444000.

     

    The pbix file is attached.

     

    If you have any other questions please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!