dax banding - calculated column Customers[Age] not being recognised by intellisense

[Peta_g]

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

[Ashish_Mathur]

Hi,

Share a sample dataset and show the expected result.

[Peta_g]

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.

[MattAllington]

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. 

[Peta_g]

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

[MattAllington]

I was not referring to the age group formula, I was referring to the Age column.  

This works in my version 

Age = VAR age = ROUNDDOWN((DATE(2003,1,1)-Customers[BirthDate])/365,0)

return age

I was suggesting you try this as a work around

Age = VAR age = ROUNDDOWN((value(DATE(2003,1,1))-value(Customers[BirthDate]))/365,0)

return age

Regarding the issue with the age bands, it is hard to say what is wrong without seeing your workbook.  If you can email it to me I will take a look.  Or you can post it here and I or someone else will help.

[Peta_g]

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 

[v-cherch-msft]

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

[MattAllington · Accepted 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.

[Peta_g]

oh my goodness - what a dufuss I am!

cheers & thanks very much

[Peta_g]

Hi Ashish

[Zubair_Muhammad]

@Peta_g

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],
    ", "
)

[Peta_g]

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+

[Peta_g]

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