Forum Discussion
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")
))
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.
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,
6 Replies
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.
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]))))