Flag Multiple Entries on Multiple Columns

[RAdams]

I've got some Product Data that contains Product Code, UMseq and UM. 

I'm only allowed to have one UM per ProductCode and UMSeq. In the example below, CRA286 and UMseq has two UM's. This is incorrect and I would like to flag this. I've been racking my head but can't figure out the best way to do this. The flag can be 1/0. Any help would be appreciated. 

[amitchandak · Accepted solution]

@RAdams ,

as column - this will flag all more than one rows
flag = if(countx(filter(Table,[product] = earlier([product]) && [UMSeq] = earlier([UMSeq])),[um]) >1, True(), False())

Measure

use this with product or UMSeq or UM

False = countx(filter(summarize(Table,[product],[UMSeq], "_1", calculate(count([UMSeq]),allexcept(Table,[product],[UMSeq]))),[_1]>1),[product])

[v-jingzhang]

Hi @RAdams , has my solution solved this problem? If so, kindly accept it as the solution to help close this topic. Otherwise if you are still confused about it, please provide more details about how it is going now. Thanks a lot.

Regards,
Jing

[RAdams]

Thanks so much for all your help. I've been working on this but I'm still having issues. 

Here is the Column Code I'm using:

 Flag Count Test = 
IF(
     COUNTX(
         filter('Canopy PBI UM Setup',
         'Canopy PBI UM Setup'[productcode] = earlier('Canopy PBI UM Setup'[productcode]) && 
         'Canopy PBI UM Setup'[umseq] = earlier('Canopy PBI UM Setup'[umseq])),
         [um]) 
         > 1, 
         "TRUE", 
         "FALSE")

However, Im getting these results. Row 4 - 6 should be giving me a Flag of 1 but it's not:

The Multi Flag Column is just the Countx Code. I wanted to see what the total was. Obviously, its not 1 or 0! 

Any more help would be apprecaited. Just an FYI, I was getting the same results with all the suggestions that were given. 

Thanks again! R

[v-jingzhang · Accepted solution]

Hi @RAdams 

Count() and Countx() count the number of cells in a column that contain non-blank values. They will count the duplicate values. It seems your data has duplicate um values per productcode and umseq, so Distinctcount() is more suitable. Please try below column codes, this should work.

flag = 
IF (
    CALCULATE (
        DISTINCTCOUNT ( Table1[um] ),
        FILTER (
            Table1,
            Table1[productcode] = EARLIER ( Table1[productcode] )
                && Table1[umseq] = EARLIER ( Table1[umseq] )
        )
    ) > 1,
    1,
    0
)

 

Please take note that DISTINCTCOUNT() will include the Blank value. If you have blank um value in your table and you want to count it as a distinct value, you could use DISTINCTCOUNT() as above. Otherwise if you don't want to count a blank value, you could use DISTINCTCOUNTNOBLANK() to replace DISTINCTCOUNT() in the codes.

Best Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[v-jingzhang]

Hi @RAdams 

Amit's column and measure DAX codes should work well.

Additionally, you could also try the following measure code.

M_Flag =
IF (
    CALCULATE (
        COUNT ( 'Table'[UM] ),
        ALLEXCEPT ( 'Table', 'Table'[Product Code], 'Table'[UMseq] )
    ) > 1,
    1,
    0
)

 

Kindly let me know if this helps.

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[amitchandak · Accepted solution]

@RAdams ,

as column - this will flag all more than one rows
flag = if(countx(filter(Table,[product] = earlier([product]) && [UMSeq] = earlier([UMSeq])),[um]) >1, True(), False())

Measure

use this with product or UMSeq or UM

False = countx(filter(summarize(Table,[product],[UMSeq], "_1", calculate(count([UMSeq]),allexcept(Table,[product],[UMSeq]))),[_1]>1),[product])