I'm learning DAX from @MattAllington 's SuperCharge PBI (p 148) & it's great but I'm having a problem creating the following calc col on Customers in AdventureWorks:
AgeGroup = CALCULATE(
Customers[Age] >= AgeBands[Low]
&& Customers[Age] < AgeBands[High]
I'm getting the error: "A single value for column 'Age' in table 'Customers' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
Customers[Age] is not being recognized by intellisense. This is also a calc col (working ok) with the formulae:
Age = ROUNDDOWN((DATE(2003,1,1) - Customers[BirthDate])/365,0) - data type & format: whole no.
I've also referred to Marco Russo's https://www.sqlbi.com/articles/grouping-transactions-by-age-of-customers-in-dax/ & am having the same problem.
thanks for any advice.
Solved! Go to Solution.
You are right Cherie, however I also provide support to anyone that purchases my book and has problems with the exercise.
Peta, you have written you "column" as a measure instead of a column.
Seems like you have overlapping Age Bands
Give this a try
AgeGroup = CONCATENATEX ( CALCULATETABLE ( VALUES ( AgeBands[Band] ), FILTER ( AgeBands, Customers[Age] >= AgeBands[Low] && Customers[Age] < AgeBands[High] ) ), AgeBands[Band], ", " )
I definitely haven't got overlapping bands & your code produces the same error message - It's not recognising [Age], though I have been able to place this column on rows in a matrix
|0||20||Less than 20|
|20||30||20 to less than 30|
|30||40||30 to less than 40|
|40||50||40 to less than 50|
|50||60||50 to less than 60|
|60||70||60 to less than 70|
going further with the exercise in @MattAllington 's book, I am now having the same sort of problem when using VAR, it's now not recognizing Customers[BirthDate]:
Is this a bug?
I have re-installed PBI Dec
Sorry Ashish I didn't show the expected result.
My prior reply shows the data (I hope this is what you meant) & the result required is (though the measure s"Customers that have purchased" & "Total Sales' havn't been included in this post/problem:
I'm just trying to get a calc column on Customers showing the AgeGroup (band as per above calculated on the Age column - see first post)
You don’t mention your version of PBI Desktop. I too have seen this behaviour and I think it is a bug. But I recall the bug is gone from November 2018 - I can’t be 100% sure though. The issue (was) that subtracting dates wasn’t working. You can solve it by wrapping each date in a VALUE() function.
Thanks for your response.
I'm using the current December update (free version)
Unfortunately your work-around doesn't seem to have helped. Please check below that I got it right:
[Age] seems to have calculated correctly - it's not throwing an error in the Customers table.
I was not referring to the age group formula, I was referring to the Age column.
This works in my version