How to make distinct count value with filter as constant

Hey Georgia_H ,

I changed the denominator and the division measure in my example. Please be aware that I only used two metrics inside both measures to create a virtual table.

The denominator now is more or less a constant:

denominator = 
var metricsToCount = {"PCM01" , "PCM23"}
var metricsCount = COUNTROWS( metricsToCount )
return metricsCount

You might expand the definition of the denominator like so

denominator check = 
var metricsToCount = {"PCM01" , "PCM23"}
var metricsCount = COUNTROWS( metricsToCount )
return 
IF( FIRSTNONBLANK( 'Table'[Metrics_ID] , 'Table'[Metrics_ID] ) in metricsToCount
    , metricsCount
    , BLANK()
)

The division measure now looks like this:

division = 
var metricsToCount = {"PCM01" , "PCM23"}
var metricsCount = COUNTROWS( metricsToCount )
return

AVERAGEX(
    'Table'
    , if( FIRSTNONBLANK( 'Table'[Metrics_ID] , 'Table'[Metrics_ID] ) in metricsToCount
        , DIVIDE( 'Table'[Avg Compliance %]  , metricsCount )
        , BLANK()
    )
) * 100

 The table visual looks like this:

Of course, it's possible to create a table from the 12 metrics, then you do not have to repeat the definition in both measures, but if they are just used in two measures I would probably use the measure definition.

As the metric is used inside your table, it will contribute to the current filter context, but the current filter context will not be expanded, for this reason the DISTINCTCOUNT will return 1. 

Hopefully, this will help to tackle your challenge

Regards,

Tom