COUNTIFS to mark duplicates in PBI

[cksmth]

Hi!

I am trying to replicate the below formula in Power BI. I am wanting to match the original use case which is to mark rows that are unique based off the criteria set. In this case the criteria are:

Column C = Project ID

Column S = Product Group

Column AY = Line Created Week

=IF(COUNTIFS(C$2:C2,C2,AY$2:AY2,AY2,S$2:S2,S2)=1,"X",IF(COUNTIFS(C$2:C2,C2,AY$2:AY2,AY2,S$2:S2,S2)>1,""))

Any help is appreaciated as this has me stumped! 🙂

[Arul · Accepted solution]

@cksmth ,

try this calculated column,

Calculated Column= 
IF(
    COUNTROWS(
        FILTER(
            'YourTable',
            'YourTable'[Project ID] = EARLIER('YourTable'[Project ID]) &&
            'YourTable'[Product Group] = EARLIER('YourTable'[Product Group]) &&
            'YourTable'[Line Created Week] = EARLIER('YourTable'[Line Created Week])
        )
    ) = 1,
    "X",
    BLANK()
)

[cksmth]

Thank you both so much!

[Anonymous · Accepted solution]

Hi,

Thanks for the soltuion @Arul  provided, and i want to offer some more information for user to refer to.

hello @cksmth , you can refer to the following solution.

Column =
VAR a = [Project ID]
VAR b = [Project Group]
VAR c = [Line Created Week]
VAR d =
    COUNTROWS (
        FILTER (
            table,
            [Project ID] = a
                && [Project Group] = b
                && [Line Created Week] = c
        )
    )
RETURN
    SWITCH ( TRUE (), d = 1, "X", d > 1, BLANK () )

If the solutions @Arul  and i offered help you solve the problem, please consider to mark them as a solution.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Arul · Accepted solution]

@cksmth ,

try this calculated column,

Calculated Column= 
IF(
    COUNTROWS(
        FILTER(
            'YourTable',
            'YourTable'[Project ID] = EARLIER('YourTable'[Project ID]) &&
            'YourTable'[Product Group] = EARLIER('YourTable'[Product Group]) &&
            'YourTable'[Line Created Week] = EARLIER('YourTable'[Line Created Week])
        )
    ) = 1,
    "X",
    BLANK()
)