cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Peta_g Frequent Visitor
Frequent Visitor

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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
13 REPLIES 13
Super User
Super User

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

@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 Frequent Visitor
Frequent Visitor

Re: dax banding - calculated column not recognised by intellisense

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

020Less than 20
203020 to less than 30
304030 to less than 40
405040 to less than 50
506050 to less than 60
607060 to less than 70
7099970+
Peta_g Frequent Visitor
Frequent Visitor

Re: dax banding - calculated column not recognised by intellisense

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]:VAR.JPG

 

 

Is this a bug?

I have re-installed PBI Dec

 

Super User
Super User

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

Hi,

 

Share a sample dataset and show the expected result.

Peta_g Frequent Visitor
Frequent Visitor

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

Hi Ashish

 

BirthDate.JPGVAR.JPG

Peta_g Frequent Visitor
Frequent Visitor

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

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:

AgeBands.JPG

 

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.

 

Super User
Super User

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

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. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Peta_g Frequent Visitor
Frequent Visitor

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

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.

Age.JPG

AgeGroup.JPG

Thanks

Peta

 

 

Super User
Super User

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

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.


* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.