topic Re: Formula works except for 1 record in DAX Commands and Tips

Formula works except for 1 record

AkameNoGamma — Fri, 23 Feb 2024 14:58:36 GMT

Hi, i need help figuring this out. screenshot 1 is my excel source file and my main concern was for M124452. when i apply my formula (screenshot 2) everything works out including for M116572, but for some reason, M124452 keeps returning the previous department under "CBE Programs" instead of "Amex US" (screenshot 3). M116572's previous department is returning correct under "quest". for all records, only M124452 shows the incorrect previous department, and im out of ideas right now on how to fix this.

Screenshot1

Screenshot2

Screenshot3

Re: Formula works except for 1 record

AlexisOlson — Fri, 23 Feb 2024 16:33:37 GMT

The FILTER is returning a table with both of the M124452 rows and then you're taking the maximum over the two. Because you are taking the MAX of a text column, it's using alphabetical ordering and chooses CBE Programs since C comes after (is "greater than") A. Note that for M116572, Q > C, so you get "Quest".

There are lots of ways to approach this if you want to take the max by date rather than max alphabetically.

Re: Formula works except for 1 record

AkameNoGamma — Fri, 23 Feb 2024 16:46:42 GMT

I never knew it was it's purposes 😄 all of my codings came from ChatGPT as this is my first time doing PowerBI. I already got the gist of it and made a couple of formulas using Max, maybe because i was using it on numbers before. This is the first time i used it for Text. thanks for figuring this out 😄

additionally, i need the count of department so that is why i opted on this formula because it works. i might just need to tweak my questions to chatGPT to get the formula that i need.

Re: Formula works except for 1 record

AlexisOlson — Fri, 23 Feb 2024 18:55:16 GMT

ChatGPT isn't as good at DAX as other languages largely because DAX is so context-sensitive.

Brian Julius has some suggestions for improving things here:
https://www.linkedin.com/posts/brianjuliusdc_improve-chatgpts-dax-coding-abilities-with-activity-7107220206761959424-0NyQ/