Forum Discussion

andrewa521's avatar
andrewa521
Frequent Visitor
3 years ago

Need help with Count IF esque formula.

Hey I am having some trouble with the syntax on this one, thanks so much for any help!   Basically For each Date and for each Letter, I want to count the amount of categories that have a value grea...
Need Help
AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
3 years ago

andrewa521  You can try this formula; 

 

Distinct Categories =
CALCULATE(
    DISTINCTCOUNT(SampleData[Category]),
    FILTER(SampleData, CALCULATE(SUM(SampleData[Amount])) > 0)
)
 

 

However, you should consider setting up a best practice data model with dimension tables that will help make calculations like this a bit easier. Also please note that my solution above relies on you having a UniqueID column in your table for it to return accurate results 100% of the time. 
 
See if these blogs help explain a bit more what I mean, otherwise just reply back with your questions: