Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
Peta_g
Helper I
Helper I

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

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

View solution in original post

13 REPLIES 13
Ashish_Mathur
Super User
Super User

Hi,

 

Share a sample dataset and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

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

 

 

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

oh my goodness - what a dufuss I am!

cheers & thanks very much

Hi Ashish

 

BirthDate.JPGVAR.JPG

Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

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+

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

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.