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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BindiyaThota
Frequent Visitor

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

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.

BindiyaThota_2-1718920780872.png

 

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?

1 ACCEPTED SOLUTION
vicky_
Super User
Super User

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.

View solution in original post

2 REPLIES 2
vicky_
Super User
Super User

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.

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors