Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi Community
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(
VALUES(AgeBands[Band]),
FILTER(AgeBands,
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.
Peta
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.
Hi,
Share a sample dataset and show the expected result.
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)
thanks
P.
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.
Hi Matt
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.
Thanks
Peta
I was not referring to the age group formula, I was referring to the Age column.
This works in my version
Hi Matt
I've used the VAR formula in a new cal'd col (deleted the old one) & am still having the same problem.
I've emailed my workbook to you.
Thanks so much for your time
Hi @Peta_g
It's better if you could share the sample file and post here so that we could help further on it. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Regards,
Cherie
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.
oh my goodness - what a dufuss I am!
cheers & thanks very much
Hi Ashish
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], ", " )
Hi
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 |
70 | 999 | 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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |