cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Multiple Filters AND and OR

I am very new to PowerBI and have been tasked with recreating all of our reports from QlikSense. 

 

I was able to get a defect rate and now need to add multiple AND and OR filters. I am struggling. Below is my Defect Rate calculation and the error message I get. I also need to add in a Filter for vSalesMaster,vSalesMaster[DefectReason] is not equal to AB Damage, Freight Damage, and Concealed Damage. 

 

DefectRate = DIVIDE(
CALCULATE(
SUM ( vSalesMaster[WarrDetail.InvoiceQty] ),
FILTER (vSalesMaster,vSalesMaster[OrderType] = "W"),
FILTER (vSalesMaster,vSalesMaster[WarrDetail.DaysInField] <= "30")
),
CALCULATE(
SUM ( [InvoiceQty] ),
FILTER (VSalesMaster,vSalesMaster[OrderType] = "R")
)) 

 

Capture.PNG

3 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

The error you are getting seems related to trying to compare a Text value with a Numeric value. My guess would be that you need to remove the double quotes around your numeric value of 30.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

@Anonymous

 

In your formula, the issue is on the logic of your applied filter, your logic like: [Column]<>"A" ||  [Column]<>"B" will return you all values in [Column], which means this filter doesn't work at all.

 

Based on your description, you should apply AND logic between those "not equal" conditions:

 

UnfilterDefectRate =
DIVIDE (
    CALCULATE (
        SUM ( vSalesMaster[WarrDetail.InvoiceQty] ),
        FILTER (
            vSalesMaster,
            vSalesMaster[OrderType] = "W"
                && vSalesMaster[WarrDetail.DefectReason] <> "AB Damage"
                && vSalesMaster[WarrDetail.DefectReason] <> "Concealed Damage"
                && vSalesMaster[WarrDetail.DefectReason] <> "Claim Filed"
                && vSalesMaster[WarrDetail.DefectReason] <> "Freight Damage"
                && vSalesMaster[WarrDetail.DefectReason] <> "Goodwill"
        )
    ),
    CALCULATE (
        SUM ( [InvoiceQty] ),
        FILTER ( VSalesMaster, vSalesMaster[OrderType] = "R" )
    )
)

Regards,

 

View solution in original post

Anonymous
Not applicable

@v-sihou-msft

 

Thank you SO much. I am new to Power BI and the easiest things are tripping me up. 🙂

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

The error you are getting seems related to trying to compare a Text value with a Numeric value. My guess would be that you need to remove the double quotes around your numeric value of 30.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler

 

Thank you so much. That took care of the error message. 

 

Do you know anything about the adding the additional filters?

You can combine multiple filters like this:

 

OpenTickets = CALCULATE(COUNTROWS(Tickets),FILTER(Tickets,(Tickets[create_ticket]<=[Dates] && ISBLANK(Tickets[close_ticket_date]) || (Tickets[create_ticket]<=[Dates] && Tickets[close_ticket_date]>=[Dates]))))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler

 

I have added in the filters and I am getting an error message:A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed. Can you help me? 

 

UnfilterDefectRate = DIVIDE(
CALCULATE(
SUM ( vSalesMaster[WarrDetail.InvoiceQty] ),
FILTER (vSalesMaster,vSalesMaster[OrderType] = "W" &&
        vSalesMaster[WarrDetail.DefectReason] <> "AB Damage" ) ||
        (vSalesMaster[OrderType] = "W" &&
        vSalesMaster[WarrDetail.DefectReason] <> "Concealed Damage" ) ||
        (vSalesMaster[OrderType] = "W" &&
        vSalesMaster[WarrDetail.DefectReason] <> "Claim Filed" ) ||
        (vSalesMaster[OrderType] = "W" &&
        vSalesMaster[WarrDetail.DefectReason] <> "Freight Damage" ) ||
        (vSalesMaster[OrderType] = "W" &&
        vSalesMaster[WarrDetail.DefectReason] <> "Goodwill" )
),
CALCULATE(
SUM ( [InvoiceQty] ),
FILTER (VSalesMaster,vSalesMaster[OrderType] = "R")
)
)

@Anonymous

 

In your formula, the issue is on the logic of your applied filter, your logic like: [Column]<>"A" ||  [Column]<>"B" will return you all values in [Column], which means this filter doesn't work at all.

 

Based on your description, you should apply AND logic between those "not equal" conditions:

 

UnfilterDefectRate =
DIVIDE (
    CALCULATE (
        SUM ( vSalesMaster[WarrDetail.InvoiceQty] ),
        FILTER (
            vSalesMaster,
            vSalesMaster[OrderType] = "W"
                && vSalesMaster[WarrDetail.DefectReason] <> "AB Damage"
                && vSalesMaster[WarrDetail.DefectReason] <> "Concealed Damage"
                && vSalesMaster[WarrDetail.DefectReason] <> "Claim Filed"
                && vSalesMaster[WarrDetail.DefectReason] <> "Freight Damage"
                && vSalesMaster[WarrDetail.DefectReason] <> "Goodwill"
        )
    ),
    CALCULATE (
        SUM ( [InvoiceQty] ),
        FILTER ( VSalesMaster, vSalesMaster[OrderType] = "R" )
    )
)

Regards,

 

Anonymous
Not applicable

@v-sihou-msft

 

Thank you SO much. I am new to Power BI and the easiest things are tripping me up. 🙂

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.