Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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)
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
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)
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
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)