Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

How to put multiple filter conditions in a query?

I have a query in which the No of "Complaints" and the "closed Complaint"time in average is visible. Now I want to see the number of "open Complaint" and the "average time" until they have acre...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    8 years ago

    Many thanks. This brought me on the track. I just had to add another ;easure for the Investigation open time (InvOpen) .

    then the solution looks like:

     

    AvgOpenComplaint =

    CALCULATE  (

            AVERAGE(RawData[InvOpen]),

            FILTER (
                      ALLEXCEPT(RawData,RawData[KeyCustomer]),
                     RawData[StatusCategory] ="Investigation Open")) 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

This is the expected outcome- Instead of "StatusCategory" here it should show up the number of open complaints.
And at the right the average time of the open complaint.

Below you see the raw data. The field "StatusCategory" can be used for this measure- I guess

 

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi Anonymous,

 

For the number of open complaints, you can create below measure.

No of open complaints =
CALCULATE (
    DISTINCTCOUNT ( RawData[ComplaintNo] ),
    FILTER (
        ALLEXCEPT ( RawData, RawData[KeyCustomer] ),
        StatusCategory = "Investigation Open"
    )
)

 

For the average time of the open complaint, please provide more description about how to calculate.

 

Regards,

Yuliana Gu

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Many thanks for the first help. It works.

    For the time we do have the following figures:

     

    For Keyaccount10 the average of the 2 open Complaints should be calculated. Actual I see the average of all 5. 

     

     

    • v-yulgu-msft's avatar
      v-yulgu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      Hi Anonymous,

       

      Please refer to below measure:

      average time of the open complaint =
      CALCULATE (
          AVERAGE ( RawData[openComplaints] ),
          FILTER (
              ALLEXCEPT ( RawData, RawData[KeyCustomer] ),
              StatusCategory = "Investigation Open"
          )
      )

       

      Best regards,

      Yuliana Gu

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Many thanks. This brought me on the track. I just had to add another ;easure for the Investigation open time (InvOpen) .

        then the solution looks like:

         

        AvgOpenComplaint =

        CALCULATE  (

                AVERAGE(RawData[InvOpen]),

                FILTER (
                          ALLEXCEPT(RawData,RawData[KeyCustomer]),
                         RawData[StatusCategory] ="Investigation Open"))