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"}))
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"}))