Counting the number of IDs meeting certain criteria

[nainslie3]

Basically I have two columns that I have pulled into a matrix table in Power Bi Desktop. 

Clog_ID (a unique identifyer from my clients table) and Referral Tier Rating (from my referrals table). Clients can have multiple referrals at different tier ratings.

Im looking for a way to count those IDs where they have had support from both 'Tier 1 or 2' and 'Tier 3' referals and display as a card on its own.

I've tried adding an advanced filter that only counts those that have 2 referral ratings but it is possible for clients to have multiple referrals into 'Tier 1 or 2' and 'Tier 3' so therefore was including those in the count.

Can anyone offer a solution or point me in the right direction?

Thanks

[v-shex-msft]

Hi @nainslie3,

You can refer to below formula to filter the specific item.(based on slicer and "referral tier rating")

Selector table:

Selector = VALUES(IDRefer[Referral Tier Rating]) 

Measure:

Is Contains(Dynamic) = 
var temp=CONCATENATEX(ALLSELECTED(Selector[Referral Tier Rating]),[Referral Tier Rating],",")
return
if(HASONEVALUE(Selector[Referral Tier Rating]),
if(temp = CONCATENATEX(FILTER(ALLSELECTED(IDRefer),[Clog_ID]=MAX(IDRefer[Clog_ID])),[Referral Tier Rating],","),"Y",BLANK()),
if(temp = CONCATENATEX(FILTER(ALL(IDRefer),[Clog_ID]=MAX(IDRefer[Clog_ID])),[Referral Tier Rating],","),"Y",BLANK()))

Regards,

Xiaoxin Sheng

[Phil_Seamark · Accepted solution]

Hi @nainslie3

There are a bunch of different ways you can do this. 

One option is to create a new calculated column that creates a 1 / 0 value that you can add to the visual filters of your card

My Filter = IF(
                CALCULATE(
                    COUNTA('Referrals'[Referral Tier rating]),
                    ALL('Referrals'),
                    'Referrals'[Clog_ID]=EARLIER('Referrals'[Clog_ID])
                    )=2,
               1,
               0)

[nainslie3]

Hi @Phil_Seamark

Thanks for the reply, this is exactly what I needed. Only problem I have now is when there is more than one referral into 'Tier 1 or 2' the formula is still counting them.

Is there something I can add to the formula so that it only counts distinct values?

Kind regards

[Phil_Seamark]

HI @nainslie3

You could add this calculated measure that counts the distinct clog_id records where the filter column has a value

Measure = CALCULATE(DISTINCTCOUNT('Referrals'[Clog_ID]),'Referrals'[My Filter]=1)