Multiple Columns Error

[Lamarandteddy]

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)

    )

[v-huijiey-msft · Accepted solution]

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!

[lbendlin]

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.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.

[Lamarandteddy]

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]

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]

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]

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]

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Lamarandteddy]

Building	Business Area	Account_Key	Account	Exercise Type	Month	Value
Office Building 4	Corporate	Account_Generator Maintenance	Generator Maintenance	Budget	1-Jan-22	$0

[Lamarandteddy]

Here is my expected output 

[lbendlin]

I only see one row of data with a 0 value?

[Lamarandteddy]

Here is another one:

Building	Business Area	Account_Key	Account	Exercise Type	Month	Value
Office Building 4	Corporate	Account_Furniture/Cubicle Refurbish	Furniture/Cubicle Refurbish	Forecast	1-Feb-23	$0
Office Building 4	Corporate	Account_Parking Lot Maintenance	Parking Lot Maintenance	Forecast	1-Feb-23	$0
Office Building 4	Corporate	Account_Plumbing Work	Plumbing Work	Forecast	1-Feb-23	$0
Office Building 4	Corporate	Account_Multimedia AV Repairs	Multimedia AV Repairs	Forecast	1-Feb-23	$0
Office Building 4	Corporate	Account_Light Bulbs/Electrical Supp.	Light Bulbs/Electrical Supp.	Forecast	1-Feb-23	$0
Office Building 4	Corporate	Account_Other General Supplies	Other General Supplies	Forecast	1-Feb-23	$0
Office Building 4	Corporate	Account_Ergonomic Purchases	Ergonomic Purchases	Forecast	1-Feb-23	$0
Office Building 4	Corporate	Account_Contract Food/Beverage/Vending	Contract Food/Beverage/Vending	Forecast	1-Feb-23	$0
Office Building 4	Corporate	Account_Courier & Freight Services	Courier & Freight Services	Forecast	1-Feb-23	$0
Office Building 4	Corporate	Account_Water/Sewer	Water/Sewer	Forecast	1-Feb-23	$0
Office Building 4	Corporate	Account_Waste Management	Waste Management	Forecast	1-Feb-23	$120
Office Building 4	Corporate	Account_IT Outside Services & Contracts	IT Outside Services & Contracts	Forecast	1-Feb-23	$1,582
Office Building 4	Corporate	Account_Affiliate Contracts	Affiliate Contracts	Forecast	1-Feb-23	($633)
Office Building 4	Corporate	Account_Tenant Leases	Tenant Leases	Forecast	1-Feb-23	($6,682)
Office Building 4	Corporate	Account_Building Rent	Building Rent	Forecast	1-Feb-23	$168,750
Office Building 4	Corporate	Account_CAM	CAM	Forecast	1-Feb-23	$8,438
Office Building 4	Corporate	Account_Property Tax for Owned Real Property	Property Tax for Owned Real Property	Forecast	1-Feb-23	$13,590
Office Building 4	Corporate	Account_Property Tax For Leased Facilities	Property Tax For Leased Facilities	Forecast	1-Feb-23	$4,830

[lbendlin]

With filter

Without filter

[Lamarandteddy]

Building	Business Area	Account_Key	Account	Exercise Type	Month	Value
Office Building 4	Corporate	Account_Generator Maintenance	Generator Maintenance	Budget	1-Jan-23	$0
Office Building 4	Corporate	Account_Landscaping	Landscaping	Budget	1-Jan-23	$0
Office Building 4	Corporate	Account_Snow Removal	Snow Removal	Budget	1-Jan-23	$0
Office Building 4	Corporate	Account_Multimedia AV Repairs	Multimedia AV Repairs	Budget	1-Jan-23	$0
Office Building 4	Corporate	Account_Misc R&M	Misc R&M	Budget	1-Jan-23	$16,687
Office Building 4	Corporate	Account_Building/Ground Maintenance	Building/Ground Maintenance	Budget	1-Jan-23	$1,663
Office Building 4	Corporate	Account_Electrical Work	Electrical Work	Budget	1-Jan-23	$2,345
Office Building 4	Corporate	Account_Elevator Maintenance	Elevator Maintenance	Budget	1-Jan-23	$835
Office Building 4	Corporate	Account_Equipment Repairs & Maint	Equipment Repairs & Maint	Budget	1-Jan-23	$795
Office Building 4	Corporate	Account_Furniture/Cubicle Refurbish	Furniture/Cubicle Refurbish	Budget	1-Jan-23	$500
Office Building 4	Corporate	Account_HVAC Maintenance	HVAC Maintenance	Budget	1-Jan-23	$14,569
Office Building 4	Corporate	Account_Janitorial Services	Janitorial Services	Budget	1-Jan-23	$31,378
Office Building 4	Corporate	Account_Parking Lot Maintenance	Parking Lot Maintenance	Budget	1-Jan-23	$410
Office Building 4	Corporate	Account_Plumbing Work	Plumbing Work	Budget	1-Jan-23	$232
Office Building 4	Corporate	Account_Window Cleaning	Window Cleaning	Budget	1-Jan-23	$1,232
Office Building 4	Corporate	Account_Furniture/Cube Moves/Installations	Furniture/Cube Moves/Installations	Budget	1-Jan-23	$7,913
Office Building 4	Corporate	Account_Copier/Fax Paper	Copier/Fax Paper	Budget	1-Jan-23	$650
Office Building 4	Corporate	Account_Housekeeping Supplies	Housekeeping Supplies	Budget	1-Jan-23	$2,351
Office Building 4	Corporate	Account_Document Destruction	Document Destruction	Budget	1-Jan-23	$725
Office Building 4	Corporate	Account_Electric	Electric	Budget	1-Jan-23	$12,649