Nested IF and COUNTIF in Power BI

[Anonymous]

Hi,

I'm trying to recreate a formula used in excel in Power BI, the formula I want to use is =IF(COUNTIF($C$2:$C2, C2)>1,0,1) but I can't seem to find a way to easily recreate this in Power BI, below is an image of how the formula works in excel.

Wherever a contract number is duplicated a zero appears which makes filtering easy. I'm aware you can use remove duplicates in Power BI but I would prefer to use a new column or measure in Power BI as it will make it easier for me when the data refreshes.

Is this something that can be done with Power BI, any help would be greatly appreciated.

Kind Regards,

Chris

[PattemManohar · Accepted solution]

@Anonymous  Please try to post your sample data in copiable format.

Here is the steps to resolve your query.

Create an Index field in Power Query Editor and then create a new column in Modeling tab (DAX) as below

Flag = 
VAR _Check = CALCULATE(COUNTROWS(Test198DupCheck),FILTER(Test198DupCheck,Test198DupCheck[ContractNo]=EARLIER(Test198DupCheck[ContractNo]) && Test198DupCheck[Index]<EARLIER(Test198DupCheck[Index])))
RETURN IF(_Check>=1,0,1)