Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
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] )
)
)
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] )
)
)
@Anonymous
Try this formula
Formula = CALCULATE ( COUNTROWS ( DataDump1 ), ALLEXCEPT ( DataDump1, DataDump1[language pair], DataDump1[subject matter] ) )
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!
Hi @Anonymous,
Did @Zubair_Muhammad's solution work? You need to use it in a visual?
Best Regards,
Dale
No, it didn't work for me. I was able to find a different solution. Thanks for looking into this for me! I really appreciate.
@Anonymous
Try adding subject matter in above formula
Formula = CALCULATE ( COUNTROWS ( DataDump1 ), ALLEXCEPT ( DataDump1, DataDump1[language pair], DataDump1[vendor name], DataDump1[subject matter] ) )
@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.