Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter table by selected client's region

Hello,

 

I have the following issue, which I have been trying to solve for a while. After months lurking the forums, I figured it was time to post my first question.

 

My data has orders, clients, and regions among other attributes. I have a matrix visual with salespeople in the rows and my order measures on the columns. I want to design a measure such that when a client is selected in another visual, the matrix visual cross-filters to show the amount of orders for that client's region.

 

This has been my approach so far, or variants:

 

CALCULATE (
    SUM ( Sales[Orders] );
    ALL ( 'Clients'[ClientCode] );
    VALUES ( 'Clients'[Region] )
)

I have tried changing the order of the calculate modifiers, I have tried using SELECTEDVALUE( ) and LOOKUPVALUE( ), but to no avail.

The requirement seems easy enough but filters must be overriding or something because I cannot wrap my head around the inner workings of this formula.

 

Any help or pointers in the right direction would be extremely appreciated.

Thank you very much in advance and kind regards,

 

Alex

  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    Try removing filters from all 'Clients'

     

    CALCULATE (
    SUM ( Sales[Orders] );
    ALL ( 'Clients' );
    VALUES ( 'Clients'[Region] )
)

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    This expression is ignoring filter applied to 'Clients'[ClientCode] only, all other filters on attributes within the 'Clients' dimension are preserved.

    1. replying filters by  VALUES ( 'Clients'[Region] ) is not necessary, as this filter was not ignored by ALL( 'Clients'[ClientCode] )
    2. adding any other attribute from the 'Clients' dimension, for example, 'Clients'[City] would add it to filter context and result in every 'Clients'[ClientCode] duplicating SUM( Sales[Orders] ) value for this City.
    3. To ensure the above is not happening you need to ignore all filters coming to form Clients Dimension and apart from the 'Clients'[Region].
    CALCULATE (
    SUM ( Sales[Orders] );
    ALL ( 'Clients'[ClientCode] );
    VALUES ( 'Clients'[Region] )
)

    In reference to the workshop that you have been attending, this is true and you should do it whenever possible or logical, it brings most benefits when you preforming iterations.

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

3 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

     

    Try removing filters from all 'Clients'

     

    CALCULATE (
    SUM ( Sales[Orders] );
    ALL ( 'Clients' );
    VALUES ( 'Clients'[Region] )
)

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Mariusz,

       

      Thank you so much, that worked wonders!

       

      However, could you explain a little bit why it works, please? I have heard in some DAX workshops that you should not filter full tables, but instead only the columns you intend to use. Is your answer an exception to this?

       

      Thanks again!

       

      Alex

      • Mariusz's avatar
        Mariusz
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi Anonymous 

         

        This expression is ignoring filter applied to 'Clients'[ClientCode] only, all other filters on attributes within the 'Clients' dimension are preserved.

        1. replying filters by  VALUES ( 'Clients'[Region] ) is not necessary, as this filter was not ignored by ALL( 'Clients'[ClientCode] )
        2. adding any other attribute from the 'Clients' dimension, for example, 'Clients'[City] would add it to filter context and result in every 'Clients'[ClientCode] duplicating SUM( Sales[Orders] ) value for this City.
        3. To ensure the above is not happening you need to ignore all filters coming to form Clients Dimension and apart from the 'Clients'[Region].
        CALCULATE (
    SUM ( Sales[Orders] );
    ALL ( 'Clients'[ClientCode] );
    VALUES ( 'Clients'[Region] )
)

        In reference to the workshop that you have been attending, this is true and you should do it whenever possible or logical, it brings most benefits when you preforming iterations.

         

        Best Regards,
        Mariusz

        If this post helps, then please consider Accepting it as the solution.

        Please feel free to connect with me.
        Mariusz Repczynski