Calculated Column on a Measure not returning correct values

[PowerAuto82]

Hi,

I previously raised the following question

https://community.powerbi.com/t5/Desktop/Dynamic-Calculate-Age-at-point-of-time-date-selection/m-p/2...

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

[parry2k · Accepted solution]

@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.

[parry2k]

@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.

[PowerAuto82 · Accepted solution]

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
)

[parry2k · Accepted solution]

@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.

[PowerAuto82]

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?

[PowerAuto82]

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?