Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Countifs in powerbi?

Here is my formula I use in excel to count the number of unique vendors with criteria language pair and subject matter.

 

=COUNTIFS(DataDump1!$C$2:$C$139526,A2,DataDump1!$E$2:$E$139526,B2)

 

Here is the data I am pulling from

 

I am trying to the count the amount of vendors with the criteria (language Pair & Subject Matter). 

 

Can someone help me write a formula in power bi that will count the unique amount of vendors with two criteria (language Pair & Subject Matter)?

 

Thank you!

  • Anonymous's avatar
    Anonymous
    8 years ago

    This is what I was looking for:

     

    =
    CALCULATE (
    COUNTROWS ( DataDump1 ),
    FILTER (
    DataDump1,
    DataDump1[languagePair] = EARLIER ( DataDump1[LanguagePair] )
    ),
    FILTER (
    DataDump1,
    DataDump1[Subject Matter] = EARLIER ( DataDump1[Subject Matter] )
    )
    )

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    This is what I was looking for:

     

    =
    CALCULATE (
    COUNTROWS ( DataDump1 ),
    FILTER (
    DataDump1,
    DataDump1[languagePair] = EARLIER ( DataDump1[LanguagePair] )
    ),
    FILTER (
    DataDump1,
    DataDump1[Subject Matter] = EARLIER ( DataDump1[Subject Matter] )
    )
    )

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Anonymous

     

    Try this formula

     

    Formula =
CALCULATE (
    COUNTROWS ( DataDump1 ),
    ALLEXCEPT ( DataDump1, DataDump1[language pair], DataDump1[subject matter] )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Zubair_Muhammad

       

      Thank you for your response, however this didn't return the correct result.

       

      I am looking for something like this, if row 1 has the vendor name of A+ Global Solutions and the language pair is chinese (China) -> English (United States) and the subject matter is IP- Non-Patent. Then I am looking for a formula to look through all the data and return how many times that combination is present in the data. 

       

      Can you provide a formula to calculate that? I am new to DAX and would really appreciate it!

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        Anonymous

         

        Try adding subject matter in above formula

         

        Formula =
CALCULATE (
    COUNTROWS ( DataDump1 ),
    ALLEXCEPT (
        DataDump1,
        DataDump1[language pair],
        DataDump1[vendor name],
        DataDump1[subject matter]
    )
)