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

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

Hi Anonymous,

 

Based on current description, it's not very clear about your requirement. Please post sample data in source table and show us your desired output.

How to Get Your Question Answered Quickly

 

Regards,

Yuliana Gu

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