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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

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.


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

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

Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.