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
DJBAJG
Helper III
Helper III

Custom Column identifying a max value in another column

I've got a very large dataset with millions of rows. Within that dataset there are rows with specific combinations of data that are duplicitive. Specifically, a combination of 4 columns, which I need to report on, create duplicate rows. I need to identify these rows, which I have done, and then create another calculated column the displays an indicator (A "1" or "Yes" or anything) telling my which duplicate row has the highest value in another column, one not used in the 4 column combination.

 

Speficially, look at the attachement.

 

The column "Plan Duplicate (Concatenation)" contains the combination of the 4 columns in question. The column "Plan Duplicate (Duplicate Count)" shows the number of duplcate rows. What I want in the "Plan Duplicate (MAX Indicator) column is an indicator that populates only for the duplicate row with the highest "Enrolled Lives" value. So row 2 and 4 in the picture shold have a "1" and rows 1 and 3 should have a "0".

 

Keep in mind the dataset contains millions of rows and the duplicate count could be 2 or 20 or 200 so any hardcoding of duplicate counts is not possible. Also, this needs to function at the "Plan Duplicate(Concatenation)" level so any usage of MAX has to take that into account or else we'll be looking at the MAX of the entire million rows and getting erroneous values.

 

Here's what I've tried so far:

 

Plan Duplicate (MAX Indicator) = IF(
    ajg_ratetiervalue_partitioned[Plan Duplicate (Duplicate Count)] = 1, 1,
    IF(
        ajg_ratetiervalue_partitioned[Plan Duplicate (Duplicate Count)] > 1 &&
        COUNTROWS(FILTER(ajg_ratetiervalue_partitioned,
            EARLIER(ajg_ratetiervalue_partitioned[Enrolled Lives]) > ajg_ratetiervalue_partitioned[Enrolled Lives])),1,0))
 
This get's me the values you see in the picture of 1 on all rows which obviously isn't what I want but I fee like I need to utilize the EARLIER function some how but perhaps I'm way off. 
 
Really need help with this one. Been working on it for a few days now with not much progress.
 
Thanks. 
 
DJBAJG_0-1696876900628.png

 

3 REPLIES 3
DJBAJG
Helper III
Helper III

Still waiting for some help on this one.... anyone, anyone....

DJBAJG
Helper III
Helper III

Hello community. Still really need help on this one....

parry2k
Super User
Super User

@DJBAJG More details required. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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