Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Nested IF and COUNTIF in Power BI

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

Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    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)

1 Reply

  • PattemManohar's avatar
    PattemManohar
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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)