topic Re: Not recognizing "22-23" as text in DAX Commands and Tips

Not recognizing "22-23" as text

MindfullyLost — Tue, 22 Oct 2024 22:04:46 GMT

I'm trying to create a column based on the value of [school_year] but it isn't reading the values correctly.

Each of these DAX returns all 0 despite "22-23" clearly existing.

behavior_flag = IF( [school_year] = "22-23", 1, 0 ) behavior_flag = IF( TRIM([school_year]) = "22-23", 1, 0 ) behavior_flag = IF( CONTAINSSTRING([school_year], "22"), 1, 0 )

However, this returns all 1's:

behavior_flag = IF( CONTAINSSTRING([school_year], "-"), 1, 0 )

I also am including a screenshot for how I confirmed that it is indeed a Text column.

Re: Not recognizing "22-23" as text

Irwan — Wed, 23 Oct 2024 00:34:27 GMT

hello MindfullyLost

i am not sure but the DAX looks fine.

however, if all rows in 'school_year' column have "-", then surely all rows will return 1 as you mentioned. This means that the DAX is working properly.

Thank you.

Re: Not recognizing "22-23" as text

MindfullyLost — Wed, 23 Oct 2024 13:32:35 GMT

I'm sorry, I should have been more clear about the issue. I have values "23-24" and "24-25" throughout the dataset. Therefore, searching for a "-" does indeed work properly in that it returns all rows. The issue is that it cannot find "23-24" as evidenced by the fact that it returns all 0's.

Re: Not recognizing "22-23" as text

Anonymous — Thu, 24 Oct 2024 06:44:55 GMT

Hi MindfullyLost ,

First of all thanks to Irwan for the quick reply. I have some other thoughts to add:
It works fine through my tests.
1. you can use LEN() function to check if the number of characters is 5 or not.

Column = LEN('Table'[school_year])

2. you can check the output by following the DAX formula using the CONTAINSSTRING() function instead of the equal sign.

behavior_flag_2 = IF( CONTAINSSTRING([school_year], "22-23"), 1, 0 )

3.Try updating PBI Desktop to the latest version.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.