Need help with Count IF esque formula.

[andrewa521]

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 greater than 0., in Dax. I have shown the examples for the first three months of what I would like to appear in column E.

https://docs.google.com/spreadsheets/d/1VL_qIyGzM4fbHOyrpl45SzM3CXImctGQrL07WpyvTQU/edit?usp=sharing

Thanks again,

Andrew

[AllisonKennedy]

@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: 

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power.html