Distinct Count for Filtered Report

[BettyG]

I'm very new at this, but I really want to be able to use this for Business Analysis.

I need to find the Distinct Count of Countries that have value (EV) in the Last Quarter of the Data File, but no value (EV) in the prior 4 Quarters of the same Data File. I can accomplish this on a Table Visualization using the following Calculated Measures and Report Filters:

EV Last Qtr = TOTALQTD( [SumEnteredValue], 'Calendar'[DateKey])

EV_Prior4Qtrs2 = CALCULATE([SumEnteredValue],DATEADD(Calendar[DateKey], -1, QUARTER))

How do I get my Card Visual to show Distinct Count of 2 instead of 62?  I can't apply filters to the Card and it won't let me do it at the Page Level either.  And, I've tried writing a calculated Measure, but no success.

Thanks for your help! bettyG

[v-sihou-msft · Accepted solution]

@BettyG

You should be able to add those measures into Visual Level Filters for your Card.

I assume you have apply a filter/slicer on date. If so, you can try to create a calculated measure like:

Distinct Countries =
CALCULATE (
    DISTINCTCOUNT ( Table[Country] ),
    FILTER ( ALL ( Table[Country] ), [EV Last Qtr] > 0 && [EV_Prior4Qtrs2] <= 0 )
)

Regards,

[BettyG]

That worked perfectly!  Thanks!

As for addint the Measures to the Visual Filters:  It does let me add the Metric to the Visual Filter, but it doesn't allow me to change the criteria from "All".  I can't get the Drop down to change from "is less than" to anything else and I can't type a number in the criteria box.

But, I'm happy with the Formula you sent.  Thanks, again.  BettyG