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...
  • 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] )
    )
    )

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]
    )
)
dtartaglia's avatar
dtartaglia
Icon for Resolver I rankResolver I
8 years ago

Anonymous

 

I was curious about this issue, tried Zubair_Muhammad solution but didn't get it to work for me. My DAX knowledge is limited to testing and reading posts on this forum and I'm sure there may be a better solution but this would work: I created a calculated column that combines all 3 column values into 1:

 

Combined = Sheet1[Purchase Order Vendor] & Sheet1[Language Pair] & Sheet1[Subject Matter]

 

...then created a simple measure:

UniqueCount = DISTINCTCOUNT(Sheet1[Combined])

 

Again, I'm sure there may be a completely single DAX formula that can do this but this worked for me.