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...
  • 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

     

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