Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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)
Proud to be a PBI Community Champion
@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)
Proud to be a PBI Community Champion
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |