Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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....
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

    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.

  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    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's avatar
    Anonymous
    9 years ago

    v-sihou-msft

     

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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Greg_Deckler

 

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

 

Do you know anything about the adding the additional filters?

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

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]))))
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

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

    • v-sihou-msft's avatar
      v-sihou-msft
      Microsoft Employee
      9 years ago

      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's avatar
        Anonymous
        Not applicable
        9 years ago

        v-sihou-msft

         

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