cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nainslie3 Frequent Visitor
Frequent Visitor

Counting the number of IDs meeting certain criteria

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.

 

CommunitySS.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Counting the number of IDs meeting certain criteria

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)

refererals.png

 

card.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

4 REPLIES 4
Super User
Super User

Re: Counting the number of IDs meeting certain criteria

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)

refererals.png

 

card.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Community Support Team
Community Support Team

Re: Counting the number of IDs meeting certain criteria

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()))

 

Capture.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
nainslie3 Frequent Visitor
Frequent Visitor

Re: Counting the number of IDs meeting certain criteria

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.

 

T1.png

 

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

 

Kind regards

Super User
Super User

Re: Counting the number of IDs meeting certain criteria

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)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!