Recognizing duplicate value error with conditions

[Anonymous]

I've been trying to create a column (so that I can apply it to a slicer) to find errors in my data for the past 2 days with no luck. In the below table, every facility has a primary manufactuer from where they source a given material. If it is the primary source, it is designatied with a "1". If not, it is left "blank" (bi returns a null value). The below table in red shows an error to call out. Facility 600 has a multiple sources for material 1238226 but only one is the primary, this is perfect. 

This is what I've created so far but it isn't working

Duplicate = if(calculate(SUM('Table'[Primary?]) > 1, 'Table'[Facility]),"Duplicate","OK")

Material Number	Facility	Primary?	Manufactuer Number
1236938	353	1	1199238
1236938	353	1	1484576
1236891	353	1	1484576
2387897	521	1	1195116
3538903	689	1	905656
4689909	857	1	616196
1238226	600	1	1000040
1238226	600		1000011
1238226	600		6548865
1238226	600		57861
1238226	600		564864

[v-gizhi-msft · Accepted solution]

Hi,

Please add an index column in Query Editor to the original table first:

Then try this measure:

Measure = 
var a = CALCULATE(COUNT('Table'[Material Number]),ALLEXCEPT('Table','Table'[Material Number]))
return
IF(a=1,1,IF(MIN('Table'[Index])=MINX(ALLEXCEPT('Table','Table'[Material Number]),'Table'[Index]),1,BLANK()))

It shows:

Then try this check measure:

Duplicate = if([Measure]<> 1,"Duplicate","OK")

The result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[Greg_Deckler]

Not 100% clear on requirements. But perhaps:

Column = 
  VAR __Material = [Material Number]
  VAR __Facility = [Facility]
  VAR __Primary = [Primary?]
  VAR __Table = 
    SUMMARIZE(
      FILTER(
        'Table',
        [Material Number] = __Material &&
          [Facility] = [Facility] &&
            [Primary?] = __Primary
      ),
      [Material Number],
      [Facility],
      [Primary?],
      "__Count",COUNTROWS('Table')
    )
RETURN
  SWITCH(TRUE(),
    __Primary<>1,BLANK(),
    MAXX(__Table,[__Count]) > 1,"Duplicate OK",
    BLANK()
  )
    

[Anonymous]

@Greg_Deckler  I'm needing something that calls out the Manufactuer and Material Number (I can concatentate these two in a new column) if there are multiple primary sources for a single material in a given plant. So, for the red highlighted cells, this would be called out as an error because the plant is sourcing the same material from two primary vendors. There can be only one primary vendor for a material at a plant.

Edit:

@Greg_Deckler tried using your solution with no luck. Tried altering it abit but received alot of false positives. Took another pass at it myself with no luck