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] = "Reject" as soon as I add the second criteria the calculation don't work.

 

I am using the below dax, can some one please help me to resolve this issue.

 

"1.QTD Rejects =

CALCULATE

(CALCULATE([Submission count(FLDD)],DATESQTD(('3.Date_Final Decision Date'[Date])))

,'3.Date_Final Decision Date'[Completed Quarter] = "Yes"
,'3.Final Decision Date'[Final Decision Term] = "Reject"
,'3.Final Decision Date'[Final Decision Term] = "Pre-screen reject"
,'3.Final Decision Date'[Final Decision Term] = "Reject but invite resubmission"
,'3.Final Decision Date'[Final Decision Term] = "Pre-screen reject and transfer"
,'3.Final Decision Date'[Final Decision Term] = "Reject and transfer"
)"
 
// [Submission count(FLDD)] = unique count of submissions
 

 

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

4 Replies

  • 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."