Filtering Aggregrate for Distinct Count

[Anonymous]

Hi All,

For a single month, I can calculate the distinct count of customers with positive volume with the following measure:

Dist Mth =

CALCULATE (

DISTINCTCOUNT ( Facts[Customer CompKey] ),

Facts[Quantity (L)] > 0
)

For the equivalent quarter calculation I have hit a snag...

Dist Qtr =

CALCULATE (

DISTINCTCOUNT ( Facts[Customer CompKey] ),

Facts[Quantity (L)] > 0    //This needs done at the aggregate level i.e. SUM(Facts[Quantity (L)]) > 0

ALL(Period),

DATESINPERIOD ( Period[Calendar Date].[Date], LASTDATE ( Period[Calendar Date] ), -3, MONTH )

)

The calculation returns the correct result 99% of the time.
The issue occurs when there is positve volume in a month but the quarter is <= 0.
i.e.

Month 1    100   This row is counted

Month 2   -100   This row is excluded
Month 3        0   This row is excluded

Quarter         0   The aggregate is incorrectly reporting 1 from Month 1 instead of the correct value of 0

Any suggestions on how to filter based on an aggregate most welcome!

Cheers, Steve

[v-rzhou-msft]

Hi @Anonymous 

Could you show me your data model? Please share a sample with me by your Onedrive for Business. And you can show me the result want. This will make it easier for me to understand your requirement.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak · Accepted solution]

@Anonymous , Try like

Dist Qtr =
CALCULATE (
countx(filter(values ( Facts[Customer CompKey] ),calculate(sum(Facts[Quantity (L)])) > 0),[Customer CompKey]),
//This needs done at the aggregate level i.e. SUM(Facts[Quantity (L)]) > 0
ALL(Period),
DATESINPERIOD ( Period[Calendar Date].[Date], LASTDATE ( Period[Calendar Date] ), -3, MONTH )
)