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 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 NumberFacilityPrimary? Manufactuer Number
123693835311199238
123693835311484576
123689135311484576
238789752111195116
35389036891905656
46899098571616196
123822660011000040
1238226600 1000011
1238226600 6548865
1238226600 57861
1238226600 564864

 

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

     

3 Replies

  • 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

       

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    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