Calculated column based on measure - not working as expected

[s-turn]

Hello, I wonder if anyone can crack this condundrum!  I cannot share the workbook, so I will just have to paste the DAX code, and images, here.

I created a measure called LastInvolvement, which calculates the date that customers last interacted with us.  This measure works fine.  Since it's a measure, the values it shows will obviously vary depending on the filter context (which may explain the problems?).

I then created a calculated column in my All_Contacts table (a table listing all customers) called LastInvolvementGroup - see below:

LastInvolvementGroup = 
SWITCH(TRUE(),
All_Contacts[LastInvolvement] >= date(2020,12,16),"01 year or less",
All_Contacts[LastInvolvement] >= date(2019,12,16),"02 years or less",
All_Contacts[LastInvolvement] >= date(2018,12,16),"03 years or less",
All_Contacts[LastInvolvement] >= date(2016,12,16),"05 years or less",
All_Contacts[LastInvolvement] >= date(2011,12,16),"10 years or less",
not(isblank(All_Contacts[LastInvolvement])), "10 years plus"
)

This used to work as intended, i.e. see below chart, where LastInvolvementGroup is used as the

colour legend.

LastInvolvementGroup as a legend - how it used to work

However, recently it has stopped working in this way, and the above chart now suggests that everyone is in "01 year or less".  Can anyone suggest why?

Many thanks,

Sophia

[SpartaBI]

I meant asa calculatef colymn in the data view,not in a visual. Can you check there and send a screenshot

[s-turn]

Ah, I see.  OK, I've just done that, but it still displays the latest date for the entire dataset, not for each individual:

I suppose this might be because the measure is built on other measures - so perhaps I will have to make calculated columns for each of them!  Weird how this used to work - I'm not sure what could have changed, but perhaps I'll never know!

[SpartaBI]

ok, happy to see my 1st guess was right 🙂 in the 1st comment I sent
"You didn't share the measure itself, but my guess is that your measure returns the max of everyone somehow. As it is a measure on a column there is context transition so all the values in that row become the filter context. Put only the measure on a column and see what is the result and update me"

I'm sure it's not hard to solve but I think best If I will see the model.
We can do a zoom / teams if you want

[s-turn · Accepted solution]

Hello again!  I've worked out what the problem is, and why it used to work.  It was actually because the relationship between AllContacts and AllEventBookings had been broken, so the part of the measure looking at LastEventBookingDate was displaying the same value for absolutely everybody.  I've now fixed this and it seems to be working as it used to - phew!  Thank you for spurring me on to break the problem down and look at each component - not sure I would have persevered!

[SpartaBI]

You didn't share the measure itself, but my guess is that your measure returns the max of everyone somehow. As it is a measure on a column there is context transition so all the values in that row become the filter context. Put only the measure on a column and see what is the result and update me

[s-turn]

Thanks for your reply!  The measure LastInvolvement (the one referred to in LastInvolvementGroup) is like this:

LastInvolvement = MAXX({All_Contacts[LastEventBooking],All_Contacts[LastEventDate],All_Contacts[LastSale],[Value])

 The measures referred to within LastInvolvement follow a pattern of 

LastEventBooking = CALCULATE(MAX(AllEventBookings[Booking Date]))

(and a relationship exists between AllEventBookings and AllContacts).

The odd thing is, it used to work.  That graph I posted is what I used to see when putting LastInvolvementGroup on the legend.  

I attach a screenshot of LastInvolvement on a column - I hope this is what you mean?  It does indeed show the latest date in the row context, which is what I would expect, but I thought that by creating LastInvolvementGroup as a calculated column, I would be able to create tables and charts similar to the one in my original post (and indeed it did once work!).