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

Filter on a measure

Hi,

 

I feel that this should be simple but my solution isn't working.

 

I have some records that belong to various departments that have an [Outcome] of either Within Target or Overdue.  I have a chart that shows the total  count of each for each department, broken down by [Outcome]:

ChemEnger_0-1594636086671.png

I would like to be able to filter the chart to show only the departments that have Overdue records, but still show those that are Within Target as well. I have a count of Overdue records but when I add [Count of Overdue] >0 to the visual filter, it only shows the Overdue records:

ChemEnger_1-1594636204797.png

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Filter on a measure

Hi @ChemEnger 

for example, you can try to add a column

Department has Overdue records = 
var _count = CALCULATE(COUNTROWS(Table), ALLEXCEPT(Table, Table[Department]), Table[Count of Overdue] > 0)
RETURN
IF(_count > 0, TRUE(), FALSE())

then filter by this column


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

3 REPLIES 3
Highlighted
Super User II
Super User II

Re: Filter on a measure

Hi @ChemEnger 

for example, you can try to add a column

Department has Overdue records = 
var _count = CALCULATE(COUNTROWS(Table), ALLEXCEPT(Table, Table[Department]), Table[Count of Overdue] > 0)
RETURN
IF(_count > 0, TRUE(), FALSE())

then filter by this column


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Filter on a measure

@ChemEnger , Try like

countx(filter(summarize(Table,Table[department],"_1",[Count of Overdue] ),[_1]>0),[department])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Filter on a measure

Thanks @az38,

 

I got an error when I tried to use the expression as you'd suggested:

"A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

 

I think this is because the [Count of Overdue] that I had was a measure, not a column.  I tweaked it slightly and got exactly what I needed:

Department has Overdue records =
var _count = CALCULATE(COUNTROWS(Table), ALLEXCEPT(Table, Table[Department]), Table[Outcome]="Overdue")
return
IF(_count > 0, TRUE(), FALSE())

 

All sorted now and working perfectly with the new column as a filter 👍

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors