Sort by column - Can't be more than one value ERROR

[BindiyaThota]

Hi,

I have DOB col and I am calculating the diff in months (DOB vs today) & named it 'MonthsSinceBirth' (added extra logic to retun -1 if DOB is blank).  I created another col 'MonthsSinceBirth_Display' which will display something like below.

I want to display all these fields in my Power BI table visual (except 'MonthsSinceBirth') but sort the values based on 'MonthsSinceBirth'. When I try to set 'sort by column', it throws below error.

Error: There can't be more than one value in 'MonthsSinceBirth' for the same value in 'MonthsSinceBirth_Display'.

But I don't have more than one value. It is a 1:1 value mapping. I even verified by just pulling those 2 fields in a to new table visual.

What I am missing?

[vicky_ · Accepted solution]

The error is throwing since there are two DOB values (1/31/24 and 1/25/24) for the Months since birth value of 4.
To fix this - either use days since birth, and sort by that; or change the DOB to use a single day of  the month (i.e always the 1st, or always the end of the month, which can be done using a calculated column with the expression DOB Month = EOMONTH(Table[DOB]). You can then sort the DOB month by your existing Months since Birth column.

[BindiyaThota]

Thank you for the response, @vicky_ .

'MonthsSinceBirth_Display' field logic is only based on "MonthsSinceBirth' which is inturn based on 'DOB'. Since, I am trying to set the 'MonthsSinceBirth' field as sort for 'MonthsSinceBirth_Display' field, why would it matter how the DOB field is? As long as both the months fields are good should it not have worked?

Anyhow, I just re-created the report again with the exact same logic as before and it did not throw any errros now. Not sure whats going on.

I will give your solution a try if it errors out again. Thank you!