FILTER function resulting in error - HELP

[aflip]

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.

Power BI Report 

Does anyone know why it is producing this error, and what the correct way to write the expression would be? Thanks!

[bsheffer]

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)

return

if(_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 1

[aflip]

@bsheffer Thank you so much! This helped tremendously. One more question...If I want to now see the opposite...where it exists in the previous month but does not exist in the current month, what would that look like?

[bsheffer]

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

[aflip]

I created a new forum message detailing exactly what I want to do here. Thanks so much for your help! Take a look at this if you have time:
https://community.powerbi.com/t5/Desktop/Need-to-create-measure-that-compares-between-the-selected-d...

[bsheffer]

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

[aflip]

@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])

RETURN

    EXCEPT(_Table1,_Table2)

[aflip]

@bsheffer Thank you! That did work. Now if I were to change it to be: FILTER('Sheet1',SELECTEDVALUE(Sheet1[Accrual_Period_Date]))

How can I get that to work?

[bsheffer · Accepted solution]

change your date to 

date(2023,01,01)

[aflip]

@bsheffer Thank you! That did work. Now if I were to change it to be: FILTER('Sheet1',SELECTEDVALUE(Sheet1[Accrual_Period_Date]))

How can I get that to work?