Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to put multiple filter conditions in a query?

PBI - link  question.jpg

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

6 REPLIES 6
v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

 

expected result - wvaerage time and number of an open complaint.JPGRaw data for summarizing  Invetime1.JPG

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Many thanks for the first help. It works.

For the time we do have the following figures:Investigation open sinces days.JPGInvestigation open sinces days-result .JPG

 

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

 

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors