Forum Discussion

admin_xlsior's avatar
admin_xlsior
Post Prodigy
4 years ago
Solved

Table filter with two field condition

Dear all,

 

When we have table visual, how to set filter with 2 field with OR condition ?

I have like this ->

Both are Measures so both are already a calculation of some other original data table, and I want if either of them has value, show the line (record) else hide.

But I cannot just add new measure and SUM those two and see if there is a value in it, since it is probaby negate each other (plus minus). 

 

Can we do something with the Built-in Filter from Power BI ? The Filter is act as "AND", right ? not "OR". So any trick for this kind of situation ?

 

Thanks,

 

 

 

Tips and Tricks
  • admin_xlsior's avatar
    admin_xlsior
    4 years ago

    Hi,

     

    This is solved. Just realized I actually only need to add those 2 measure with ABS() function for each. The result will be zero whenever both are zero, then I can use it in "Filter on this visual"

     

    Thanks.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    admin_xlsior , To have or using slicer you need them to come from two independent tables  

     

    then a measure like

    calculate(sum(Table[Value]), filter(Table, Table[A] in allselected(A[A]) && Table[B] allselected(B[B]) ) )\

     

    Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE

    • admin_xlsior's avatar
      admin_xlsior
      Post Prodigy
      4 years ago

      Hi,

       

      If possible, I don't want to have any addition table into it. Can we maintain this on the visual it self ? Because I think this is just a matter of display or not. But I hope I'm right.

       

      So, as mentioned, this two value are measures and in my Table Visual only listed/display these two measure and 1 more from a dimension table related to that measure. 

       

      So for example, Measure A and B are SUM of Gross and Net sales (from Order table) and the other column I included in that Table Visual is Customer (from Customer table which related to Order table). This is however only example, and what I needed is whenever the A or B has value, show the line, if both has zero value, hide it.

       

      Hope it is more clear.

       

      Thanks

       

       

       

       

       

      • admin_xlsior's avatar
        admin_xlsior
        Post Prodigy
        4 years ago

        Hi,

         

        This is solved. Just realized I actually only need to add those 2 measure with ABS() function for each. The result will be zero whenever both are zero, then I can use it in "Filter on this visual"

         

        Thanks.