Use a result of a distinct count measure as a filter

[Jesskim96]

Hi,

I'm not sure it's possible, but I want to have the option to filter a card visualisation by the results of two distinct counts.

I have a data that includes an Customer ID and a Programme ID. Each Customer ID can have participated in multiple different programmes and so there is not a distinct list. I want to be able to show in a card how many customers particpated in more than 1 programme. I have tried using a calculated table but these are not impacted by slicers from the original data which I need within the report as I want this to be dynamic based on the slicer settings applied. 

This is an example of the original data

This is the distinct count I want to do to get the number of programmes each customer has been involved in: 

This is the ideal final format that I would want within Power BI or the ability to filter on one of the programmes within a card and just show the count of customer IDs.

As I said, I'm not sure it's possible to do but any direction you can point me in would be great, thanks!

[Jesskim96]

That works thank you!

Following from this, is there a way to do the same but be able to then select only one of the Values in the Number of Programmes table within filters so I could show the Num matching customers in a card rather than a table? When I try to put it in a card the I get a blank value as I assume it needs the Values to work?

Thanks!

[johnt75]

Add the Number of Programmes column into the filter pane for the card visual and choose which number you want

[johnt75 · Accepted solution]

You would need to create a table to store and show the number of programmes, you could use something like

Number of programmes = GENERATESERIES( 1, 5 )

You can then create a measure to show the number of customers who match that number of programmes

Num matching customers =
VAR CurrentNumberOfProgrammes =
    SELECTEDVALUE ( 'Number of programmes'[Value] )
VAR CustomersWithCount =
    ADDCOLUMNS (
        VALUES ( 'Table'[Customer ID] ),
        "@num programmes", CALCULATE ( DISTINCTCOUNT ( 'Table'[Programme ID] ) )
    )
VAR Result =
    COUNTROWS (
        FILTER ( CustomersWithCount, [@num programmes] = CurrentNumberOfProgrammes )
    )
RETURN
    Result

Put the measure in a visual with the column from number of programmes.