Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Recognizing duplicate value error with conditions

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 ...
Need Help
Tips and Tricks
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    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's avatar
Greg_Deckler
Community Champion
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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