Forum Discussion

MA654654's avatar
MA654654
Regular Visitor
2 years ago

Remove interaction with slicer for specific column in Matrix

I need a solution for remove interaction with slicer for specific column in matrix table. I have a table with 5 columns from that 2 columns, I am using measure to bring data into the table. and this table has 3 rows based on last 3 years. Column 1 : Cancelled Orders . here i am using count rows to find no of orders cancelled. Totally I have 4 types of cancelled order status, cancelled before dispatch, during transit, etc

 

MY DAX IS = CALCULATE(COUNTROWS(Ordertable,ordertable[status] in {“canceleld before dispatch” , “cancelled during transit”…,} removefilters(ordertable[status]) I have a slicer Order status when i select any status only this specific column should not interact and other column values should be as per the slicer selection. I tried using ALL, ALLEXCEPT, REMOVE FILTERS, but I am getting correct values only if I select all status if I select any specific values its interacting with slicer.

Need Help

5 Replies

    • MA654654's avatar
      MA654654
      Regular Visitor
      2 years ago

      So can i create new table based on Order No and Order status and create a measure from the new table and add cancelled coloumn to exitsing matrix will work ? 

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    MA654654 , I think ALL should work here

     

    Cancelled Orders =
    CALCULATE(
        COUNTROWS(Ordertable),
        Ordertable[status] IN {"cancelled before dispatch", "cancelled during transit", ...},
        ALL(Ordertable[status])
    )
     
    ALL(Ordertable[status]) part ensures that the slicer selection on the status column does not affect the calculation of the Cancelled Orders column. This way, the Cancelled Orders column will always show the count of orders with the specified statuses, regardless of the slicer selection.
     
    • MA654654's avatar
      MA654654
      Regular Visitor
      2 years ago

      I have tried ALL function, its still interacting with slicer, I am getting correct values only if I select all status in slicer, if i select anything specific then its change

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, MA654654 

    Thanks for amitchandak and bhanu_gautam reply. You can try this measure.

    CancelledOrders = 
CALCULATE(
    COUNTROWS(Ordertable),
    Ordertable[status] IN {"cancelled before dispatch", "cancelled during transit", ...},
    REMOVEFILTERS(Ordertable[status]),
    ALLSELECTED(Ordertable)
)

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum