How to TAG duplicates using a specific criteria and rules

[Anno2019]

Hi PBI Guru's

Need help please, I know what my logic should be, I know what my result should be, but I am unable to build any formula to show the result I need.
Below is the data set up and three different scenarios. The column named [Primary File Number line TAG] is where I would like to add the formula.
Hope I can get someone to figure this out.

[v-lili6-msft · Accepted solution]

hi, @Anno2019 

You could use this formula to create a new column as below:

Primary File Number line TAG = var countsales=CALCULATE(COUNTA('Table'[Account Owner Role]),FILTER('Table','Table'[File Number]=EARLIER('Table'[File Number])&&'Table'[Account Owner Role]="Sales")) return
var highestinvoice=CALCULATE(MAX('Table'[Invoice Amount]),FILTER('Table','Table'[File Number]=EARLIER('Table'[File Number])&&'Table'[Account Owner Role]="Sales")) return
IF(countsales<>BLANK(),IF('Table'[Invoice Amount]=highestinvoice&&'Table'[Account Owner Role]="Sales","Primary","Ignore"),IF('Table'[Invoice Amount]=CALCULATE(MAX('Table'[Invoice Amount]),FILTER('Table','Table'[File Number]=EARLIER('Table'[File Number]))),"Primary","Ignore"))

Result:

and here is sample pbix file, please try it

Best Regards,

Lin

[v-lili6-msft · Accepted solution]

hi, @Anno2019 

You could use this formula to create a new column as below:

Primary File Number line TAG = var countsales=CALCULATE(COUNTA('Table'[Account Owner Role]),FILTER('Table','Table'[File Number]=EARLIER('Table'[File Number])&&'Table'[Account Owner Role]="Sales")) return
var highestinvoice=CALCULATE(MAX('Table'[Invoice Amount]),FILTER('Table','Table'[File Number]=EARLIER('Table'[File Number])&&'Table'[Account Owner Role]="Sales")) return
IF(countsales<>BLANK(),IF('Table'[Invoice Amount]=highestinvoice&&'Table'[Account Owner Role]="Sales","Primary","Ignore"),IF('Table'[Invoice Amount]=CALCULATE(MAX('Table'[Invoice Amount]),FILTER('Table','Table'[File Number]=EARLIER('Table'[File Number]))),"Primary","Ignore"))

Result:

and here is sample pbix file, please try it

Best Regards,

Lin

[Anno2019 · Accepted solution]

@v-lili6-msft 

You are a legend.  Thank you so much, it works perfectly.

If I may ask another question, I might need to open another thread though but in case you can assist I would like to take the chance to ask.

is there anyway that I can add this to the actual query?  I would like to exclude all the "ignore" from my final data table.

is this possible at all.