Forum Discussion

TuncOztuna's avatar
TuncOztuna
Frequent Visitor
3 years ago
Solved

Dax Question- All and Slicer

Hi All,

 

My question is,

why when I use the all function , slicer can not affect the Sales w All(Sales) measure, while the date slicer can affect the Sales w All(Products) measure?

 

 

Isn't the logic for all function to ignore all filters?

 

revenue measure = SUMX(sales,sales[Price]*sales[quantity])

Sales w All(Products) = CALCULATE([revenue measure],ALL(products))
Sales w All(Sales) = CALCULATE([revenue measure],ALL(sales))
 
Thanks in advance.
 

 

Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , TuncOztuna 

    According to your description, you used the date slicer, and then used different ALL() functions, which returned different results.
    For your question, I'm guessing that your table was related before, and the Sales table is a multi-terminal table.
    If we use the ALL ('Sales') table and that table is a multi-segment table, we will simultaneously clear the filter for the date table, so it will cause data inconsistencies.
    Using the 'Product' table does not affect the filtering of the date table, so the data is inconsistent.

    Therefore, you need to determine what table and what field should be placed in your all function according to your actual needs.

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • Thennarasu_R's avatar
    Thennarasu_R
    3 years ago

    Hi !
    TuncOztuna 
    In All() function apply More Logics,
    Bacially You Apply the logics in your Dax .
    Only ingnore the filter Products and Sales Table only  .Not Apply to Your date Master Table .
    You used to date Master Table in Slicer It will took the Filter .It work Based on your slicer sales Comes out put.

    You Need more Information about ALL() Function go through this Website DAX GUIDE
      https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/


    Thanks,
    Thennarasu 

4 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi , TuncOztuna 

    Yes, when we use the ALL() function, it will ignore the slicer filter . If you want to keep the slicer filter , you can use ALLSELCTED() function, like this:

    revenue measure = SUMX(sales,sales[Price]*sales[quantity])

Sales w All(Products) = CALCULATE([revenue measure],ALLSELECTED(products))
Sales w All(Sales) = CALCULATE([revenue measure],ALLSELECTED(sales))

     

    For more information, you can refer to :

    ALLSELECTED function (DAX) - DAX | Microsoft Learn

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • TuncOztuna's avatar
      TuncOztuna
      Frequent Visitor
      3 years ago

      Hi v-yueyunzh-msft,

       

      Thank you for your reply I guess I didn't explain it in enough detail.

       

      When I chose 2018 from the slicer, as seen in the attached photo.

      Sales w All(Products) = 17,380
      Sales w All(Sales) = 139,707.

       

      However, I used the all function in both, as I mentioned above.

      I would like to understand why I am getting two different results.

      • v-yueyunzh-msft's avatar
        v-yueyunzh-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi , TuncOztuna 

        According to your description, you used the date slicer, and then used different ALL() functions, which returned different results.
        For your question, I'm guessing that your table was related before, and the Sales table is a multi-terminal table.
        If we use the ALL ('Sales') table and that table is a multi-segment table, we will simultaneously clear the filter for the date table, so it will cause data inconsistencies.
        Using the 'Product' table does not affect the filtering of the date table, so the data is inconsistent.

        Therefore, you need to determine what table and what field should be placed in your all function according to your actual needs.

         

        Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

         

        Best Regards,

        Aniya Zhang

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • Thennarasu_R's avatar
    Thennarasu_R
    Icon for Responsive Resident rankResponsive Resident
    3 years ago

    Hi !
    TuncOztuna 
    In All() function apply More Logics,
    Bacially You Apply the logics in your Dax .
    Only ingnore the filter Products and Sales Table only  .Not Apply to Your date Master Table .
    You used to date Master Table in Slicer It will took the Filter .It work Based on your slicer sales Comes out put.

    You Need more Information about ALL() Function go through this Website DAX GUIDE
      https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/


    Thanks,
    Thennarasu