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
AkameNoGamma
Helper I
Helper I

Formula works except for 1 record

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

1.png

 

Screenshot2

3.png

 

Screenshot3

2.png

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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.

 

 

View solution in original post

3 REPLIES 3
AlexisOlson
Super User
Super User

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.

 

 

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.

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-710...

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.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors