Forum Discussion

Jeo_Thomas's avatar
Jeo_Thomas
Frequent Visitor
5 years ago
Solved

Filter data based on multiple criteria in same column

Hi I want to calculate QTD total count of submissions rejected.   I am able to filter the data only with one criteria in same column for example where 3.Final Decision Date'[Final Decision Term] = ...
Need Help
  • Jeo_Thomas's avatar
    Jeo_Thomas
    5 years ago

    I found a workaround I applied filter similar to like amitchandak  suggested and I appled the "Yes" filter at report level. Below is the DAX I used.

     

    QTD Rejections =
    CALCULATE([Submission count(FLDD)],
    DATESQTD(('3.Date_Final Decision Date'[Date]))
    ,FILTER('3.Final Decision Date','3.Final Decision Date'[Final Decision Term] in {"Reject", "Pre-screen reject","Reject but invite resubmission","Pre-screen reject and transfer","Reject and transfer"}))
amitchandak's avatar
amitchandak
Super User
5 years ago

Jeo_Thomas , Try like

 

CALCULATE
(CALCULATE([Submission count(FLDD)],DATESQTD(('3.Date_Final Decision Date'[Date]))),
filter( '3.Date_Final Decision Date'
,'3.Date_Final Decision Date'[Completed Quarter] in{ "Yes", "Reject", "Pre-screen reject","Reject but invite resubmission","Pre-screen reject and transfer","Reject and transfer"}
))

Jeo_Thomas's avatar
Jeo_Thomas
Frequent Visitor
5 years ago

This looks great, but "Yes" filter is from table '3.Date_Final Decision Date'[Completed Quarter] and rest of the filters are from table 3.Final Decision Date'[Final Decision Term]. amitchandak would you be able to please advice?

 

I know the naming of the table is bit confusing I have added prefix at "Date_" for the date table, sorry about that.

  • Jeo_Thomas's avatar
    Jeo_Thomas
    Frequent Visitor
    5 years ago

    amitchandak i used below dax but it is giving me error 

     

    Dax:

    1.QTD Rejects =
    CALCULATE
    (CALCULATE
    ([Submission count(FLDD)],DATESQTD(('3.Date_Final Decision Date'[Date]))),
    filter( '3.Date_Final Decision Date'
    ,'3.Date_Final Decision Date'[Completed Quarter] in{"Yes"} && FILTER('3.Final Decision Date','3.Final Decision Date'[First Decision Term] in {"Reject", "Pre-screen reject","Reject but invite resubmission","Pre-screen reject and transfer","Reject and transfer"}
    )))
     
    Error Message:
    "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
    • Jeo_Thomas's avatar
      Jeo_Thomas
      Frequent Visitor
      5 years ago

      I found a workaround I applied filter similar to like amitchandak  suggested and I appled the "Yes" filter at report level. Below is the DAX I used.

       

      QTD Rejections =
      CALCULATE([Submission count(FLDD)],
      DATESQTD(('3.Date_Final Decision Date'[Date]))
      ,FILTER('3.Final Decision Date','3.Final Decision Date'[Final Decision Term] in {"Reject", "Pre-screen reject","Reject but invite resubmission","Pre-screen reject and transfer","Reject and transfer"}))