Forum Discussion
Calculated Column on a Measure not returning correct values
Hi,
I previously raised the following question
Based on the Age DAX measure that was provided to me on the Accepted solution, I created a new Calculated Column as per below, but all the results are coming back of a value of -20 and not sure why?
Age Range =
IF('Employee'[Age] < 20, "-20",
IF('Employee'[Age] >=20 && 'Employee'[Age] <=30, "20-30",
IF('Employee'[Age]>30 && 'Employee'[Age] <=40,"31-40",
IF('Employee'[Age]>40 && 'Employee'[Age] <=50,"41-50",
IF('Employee'[Age]>50 && 'Employee'[Age] <=60,"51-60","61+")))))
The reason I want this as a calculated column because I want to use this within a Clustered Column Chart for the X-axis
Thanks
PowerAuto82 you cannot convert measure to a conditional column, You need a disconnected table and then use that for visual, check this blog post on dynamic segmentation Dynamic segmentation – DAX Patterns
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
I made a change to the current DAX to the below
Age 2 = AVERAGEX ( VALUES ( 'Calendar'[Date].[Year] ), VAR EmployeeInSegment = FILTER ( ALLSELECTED ( Employee ), VAR AgeOfEmployee = [Age] VAR SegmentForEmployee = FILTER ( 'Age Band Desc', NOT ISBLANK ( AgeOfEmployee ) && 'Age Band Desc'[Start] <= AgeOfEmployee && 'Age Band Desc'[End] >= AgeOfEmployee8 ) VAR IsCustomerInSegments = NOT ISEMPTY ( SegmentForEmployee ) RETURN IsCustomerInSegments ) VAR Result = CALCULATE ( [Age], KEEPFILTERS ( EmployeeInSegment ) ) RETURN Result )
5 Replies
PowerAuto82 keep the measure, you need a new table with the range and then use that as described in the blog post.
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
I made a change to the current DAX to the below
Age 2 = AVERAGEX ( VALUES ( 'Calendar'[Date].[Year] ), VAR EmployeeInSegment = FILTER ( ALLSELECTED ( Employee ), VAR AgeOfEmployee = [Age] VAR SegmentForEmployee = FILTER ( 'Age Band Desc', NOT ISBLANK ( AgeOfEmployee ) && 'Age Band Desc'[Start] <= AgeOfEmployee && 'Age Band Desc'[End] >= AgeOfEmployee8 ) VAR IsCustomerInSegments = NOT ISEMPTY ( SegmentForEmployee ) RETURN IsCustomerInSegments ) VAR Result = CALCULATE ( [Age], KEEPFILTERS ( EmployeeInSegment ) ) RETURN Result )
PowerAuto82 you cannot convert measure to a conditional column, You need a disconnected table and then use that for visual, check this blog post on dynamic segmentation Dynamic segmentation – DAX Patterns
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
Hi parry2k
Would you know where to start? Would I have to change the current DAX Measure I have for Age or keep that and create a new measure? Or do I just need to create a new table that holds the Age Range?
Hi parry2k
I have added the file linke to one drive below which has the new measure and table.
https://1drv.ms/u/s!AkzJiHUi2WtQfeq6weejdmOZ1Ls?e=Fb7cc0
But for some reason it is not working, when I select Year 2022 then the Employment Id Emp13 is missing?
When I select Year 2021 then the Employment Id Emp06 is missing
When Year 2019 or 2020 is either selected it seems to work fine, any ideas?