Reply
Regular Visitor
Posts: 34
Registered: ‎04-10-2018
Accepted Solution

Calculated Column Filter Issue

Hello,

I created a calulated column, which worked fine, but now I want to add a filter to that same column, and I am getting the error: "A single value for 'SAODate' in table 'Marketing Snapshot' cannot be determine. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation.

I got this error when I tried making a measure, but don't understand why I am getting it when I am using a column.

Any help would be appreciated!
Thanks,

Greg

 

 

Column prior to Filter:

SAO Aging (Today) = DATEDIFF('Marketing Snapshot'[SAODate],TODAY(),DAY)

 

 

Current formula:

SAO Aging (Today) = CALCULATE(DATEDIFF('Marketing Snapshot'[SAODate],TODAY(),DAY),
FILTER (
ALL ( 'Marketing Snapshot'[SAOYear] ),
'Marketing Snapshot'[SAOYear] = [Max SAO Year]
),
FILTER('Marketing Snapshot','Marketing Snapshot'[OpportunityOrigin]="Marketing"),
FILTER (
ALL ( 'Marketing Snapshot'[DateStampMonth] ),
'Marketing Snapshot'[DateStampMonth] = [Max DateStampMonth]
))


Accepted Solutions
Super User
Posts: 1,439
Registered: ‎06-24-2015

Re: Calculated Column Filter Issue

Hey,

 

I guess this would also calculate the value for each but just based on the values of the year 2018.

 

For this reason I would do the following
SAO Aging (Today) = 
IF(YEAR([SAODate]) = YEAR(TODAY())
,your calculation

,BLANK()
)

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post


All Replies
Super User
Posts: 1,439
Registered: ‎06-24-2015

Re: Calculated Column Filter Issue

Hey,

 

if i understand your requirement correctly you are creating the Calculated Column "SAO Aging (Today)"

 

By default there is just a ROW CONTEXT and no FILTER CONTEXT.

Using CALCULATE means that an existing ROW CONTEXT is transformed into FILTER CONTEXT

 

Actually I'm not sure what 

[Max SAO Year] and [Max DateStampMonth] are, if they are measures, it's not possible what you are trying, due to the fact that measures are not available during the creation of CALCULATED COLUMNS.

If they are columns inside the table where you define the CALCULATED COLUMN you have to use some kind of Aggregate Function around it like MAX.

 

Please be aware that using CALCULATE creates a FILTER CONTEXT, inside this FILTER CONTEXT there is just one available - the current row.

 

Hope this gives you an idea and gets you started.

 

It would also being helpful if you create some sample data, upload the pbix to onedrive or dropbox and share the link to that pbix.

 

Regards

Tom

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Highlighted
Regular Visitor
Posts: 34
Registered: ‎04-10-2018

Re: Calculated Column Filter Issue

@TomMartens

 

Thank you for that information!

 

I am thinking to simply this and tried to use an origional field that is part of the database in the filter, but got the same error as before.

I am basically trying to do the DATEDIFF calculation, but only on filtered data, as I want to only look at a subset.,

For this example I only want to look at data with SAOYear=2018, and each record's SAO Date difference from today, but aggregated and averaged.

 

Would something like this work?

 

SAO Aging (Today) = CALCULATE(DATEDIFF('Marketing Snapshot'[SAODate],TODAY(),DAY),
FILTER (
ALL ( 'Marketing Snapshot'[SAOYear] ),
'Marketing Snapshot'[SAOYear] = MAX('Marketing Snapshot'[SAOYear])
))

Super User
Posts: 1,439
Registered: ‎06-24-2015

Re: Calculated Column Filter Issue

Hey,

 

I guess this would also calculate the value for each but just based on the values of the year 2018.

 

For this reason I would do the following
SAO Aging (Today) = 
IF(YEAR([SAODate]) = YEAR(TODAY())
,your calculation

,BLANK()
)

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Regular Visitor
Posts: 34
Registered: ‎04-10-2018

Re: Calculated Column Filter Issue

@TomMartens

 

Thanks that worked!