DISTINCTCOUNT of groups with totals greater than 0

[nisuomi]

Hi,

I have a DAX problem, for some reason any of my DAX's are not working the way I need them to.

I need to calculate distinct amount of clients for account manager, which have sales over 0 - so any negative values are dropped out. And the time filtering should be working at least, and of course when selecting other filters as well. Am I thinking too complecated?

For some reson I can't this working..

measure = CALCULATE(DISTINCTCOUNT(Table1[asiakas]);FILTER(Table1;CALCULATE(sum(Table1[formula1]);ALLEXCEPT(Table1;Table1[client];Table1[month];Table1[year]))>0))

On the image, formula1 = peruslaskukaava, client = asiakas, month = kk, year = vuosi

The T1 is having the peruslaskukaava (formula1) inside it, it is just calculated on this measure.

Can't wrap my head around this. I get the amount for each customer as highlighted, but on the measure it shows the per row level sum distinctcount instead?

Cheers,
Niko

[nisuomi · Accepted solution]

Have to say, that I might have found a solution already after using a Google.

From here: https://datamaul.wordpress.com/2017/11/19/distinct-count-of-dimension-based-on-a-filtered-measure/

Product Short of Target =
VAR ProductCount =
COUNTROWS ( FILTER ( VALUES ( 'Product'[ProductCode] ), [Fulfillment] < 1 ) )
RETURN IF ( ISBLANK ( ProductCount ), 0, ProductCount )

At least it seems to be working now the way it should.. Amazing!

If anybody else has some other solution, I am willing to listen and learn 🙂 But kudos to the datamaul!

Cheers,
Niko