Forum Discussion
FILTER function resulting in error - HELP
I have a dataset consisting of lease numbers, accrual periods, production dates, and volumes by product. I'm trying to do a simple FILTER() function as a measure, but am getting this error: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." Does anyone know why? I've also tried to create a New Table with the same expression, but it returns the same error.
This is the expression:
Table1 = FILTER('Sheet1',[Accrual_Period_Date] = 01/01/2023)
I also tried:
Table1 = FILTER('Sheet1',[Accrual_Period_Date] = "01/01/2023")
Attached is the pbix with the Table1 measure and the Table1 calculated table.
Does anyone know why it is producing this error, and what the correct way to write the expression would be? Thanks!
change your date to
date(2023,01,01)
9 Replies
ok you don't need two tables. You just have to check if a product and date existing in the prior period. You can set your slicer to filter on the current period
the results I got were
measure assumes you want
1. if the period you choose in your slicer is the earliest period you have data for, then return no rows
2. if the product and date exist in the prior month (period) then do not show it
3. if the product and the date do not exist in the prior month then show that row
_test =var _tested_date = min('Sheet1'[Date])var _product = min('Sheet1'[Product])var _accrual_period_date = min('Sheet1'[Accrual_Period_Date])var _min_all_date = calculate(min('Sheet1'[Accrual_Period_Date]), all('Sheet1'))var _prior_date = dateadd('Sheet1'[Accrual_Period_Date], -1, MONTH)returnif(_accrual_period_date = _min_all_date, 0,if(calculate(COUNTROWS(Sheet1), filter(all(Sheet1), 'Sheet1'[Accrual_Period_Date] = _prior_date && 'Sheet1'[Date] = _tested_date && 'Sheet1'[Product] = _product)) = 0, 1, 0))add this measure as a visual filter set to a value of 1I don't think you can as the slicer is filtering the table to the accrual date. You can't show the prior period on that filtered table.
change your date to
date(2023,01,01)you are creating a table, not a measure so you can't use selectedvalue as that will always return a blank if there is more than one date. This table will be re-created when you refresh your data so it is not dynamic like a measure is.
I think you need to better describe what you are trying to accomplish
bsheffer Here is the bigger picture: I'm trying to compare between two accrual period dates dynamically to see if it exists in the current accrual period(for example, 01/01/2023), but not in the previous accrual period(12/01/2022), but having a slicer vertical list for Accrual_Period_Date so you can select your "current accrual period date".
This is what I tried:
NewPayments =VAR _Table1 = SELECTCOLUMNS(FILTER('Sheet1',SELECTEDVALUE(Sheet1[Accrual_Period_Date])),"LEASE",[LEASE],"Accrual_Period_Date",[Accrual_Period_Date],"Date",[Date],"Product",[Product],"NetVolume",[Net Prorated Volume, BOE])VAR _Table2 = SELECTCOLUMNS(FILTER('Sheet1',DATEADD(FILTER(LASTDATE('Sheet1'[Accrual_Period_Date]), 'Sheet1'[Accrual_Period_Date] = SELECTEDVALUE(Sheet1[Accrual_Period_Date])), -1, MONTH)),"LEASE",[LEASE],"Accrual_Period_Date",[Accrual_Period_Date],"Date",[Date],"Product",[Product],"NetVolume",[Net Prorated Volume, BOE])RETURNEXCEPT(_Table1,_Table2)