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

     

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