How to put multiple filter conditions in a query?

[Anonymous]

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 acreated.

I haven´t found the link how I can do it in the Powerr BI coummunity.  I am sure it is quite easy. if you know how to do it.

[v-yulgu-msft]

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]

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]

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]

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]

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 · Accepted solution]

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