How to combine IF and COUNTIFS function in Power Bi

[dejanzoric]

Hi, I need help how to transfer a function in excel to power bi.

I have a situation where my ID and Code are repeated in multiple combinations. I wanted to count the repetitions, but so that in the new column it shows me the value 1 for the given condition, and if the same combination appears again, it assigns me the value 0.

In Excel, I just solved it using the formula: =IF(COUNTIFS($C$2:C2;C2;$A$2:A2;A2)>1;0;1)

Where is my column in the formula:

• A - ID
• C - Code

I want to automate some processes through Power Bi and any help would be great.

Thanks in advance, Dejan

[dejanzoric]

Is there anyone who can help?

i just want to mark in new column unique id with value ="1" without duplicate . So that when I mark the value "1" in the filter, all values (ID) will be unique and not repeated.

[amitchandak]

@dejanzoric , Assume you want count ID having more than one code

Measure 

M1=  countrows(Table[Code])

M2 = countx(Values(Table[ID]), if([M1] >1, [ID], blank() ) )

[dejanzoric]

Thanks@amitchandak for helping me.

Maybe I wasn't clear in my explanation, so I'll give an example so you can understand me more easily

ID	ID2	CODE	COUNT	IF
0022211000560	Z11290588	1289666	1	1
0022211000560	Z11290594	1289666	2	0
0022211000560	Z11290605	1289666	3	0
0022211000560	Z11290588	1285392	1	1
0022211000560	Z11290594	1285392	2	0
0022211000560	Z11290605	1285392	3	0

COUNT =COUNTIFS($C$2:C2;C2;$A$2:A2;A2)

IF =IF(COUNT>1;0;1)

What I don't know how to calculate is the COUNT column. 

Thank you, Dejan