Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
dejanzoric
Frequent Visitor

How to combine IF and COUNTIFS function in Power Bi

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

3 REPLIES 3
dejanzoric
Frequent Visitor

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
Super User
Super User

@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() ) )

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

 

IDID2CODECOUNTIF
0022211000560Z11290588128966611
0022211000560Z11290594128966620
0022211000560Z11290605128966630
0022211000560Z11290588128539211
0022211000560Z11290594128539220
0022211000560Z11290605128539230

 

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.