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

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.

  • Lamarandteddy's avatar
    Lamarandteddy
    New Member
    2 years ago

    Ok, so how would I give an expression which returns all months in 2023 that have a budget value greater than $440K in aggregate form? 

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      Create your visual with all months in 2023 and their value.

       

      In your measure,

      Use what you created (personally I would use SUMMARIZE, but that's a matter of preference)  and then test if the SELECTEDVALUE month is part of that list.  If yes, return 1, otherwise return 0.

      Lastly, use your measure as a filter for the visual.

       

      Or - use the filter pane to show only months above the budget value threshold...

      • Lamarandteddy's avatar
        Lamarandteddy
        New Member
        2 years ago

        Could you give me all of the aggregate function so I can make sure the output performs correctly?