Forum Discussion

ChemEnger's avatar
ChemEnger
Icon for Advocate V rankAdvocate V
6 years ago
Solved

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]:

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:

 

 

 

Need Help
  • az38's avatar
    az38
    6 years ago

    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

3 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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

    • ChemEnger's avatar
      ChemEnger
      Icon for Advocate V rankAdvocate V
      6 years ago

      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 👍

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    ChemEnger , Try like

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