Forum Discussion

dejanzoric's avatar
dejanzoric
Frequent Visitor
2 years ago

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

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      dejanzoric
      Frequent Visitor
      2 years ago

      Thanksamitchandak 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

  • dejanzoric's avatar
    dejanzoric
    Frequent Visitor
    2 years ago

    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.