topic Filter table by selected client's region in DAX Commands and Tips

Filter table by selected client's region

Anonymous — Tue, 01 Oct 2019 07:57:07 GMT

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

Re: Filter table by selected client's region

Mariusz — Wed, 02 Oct 2019 09:22:07 GMT

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.

Re: Filter table by selected client's region

Anonymous — Wed, 02 Oct 2019 12:31:13 GMT

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

Re: Filter table by selected client's region

Mariusz — Thu, 03 Oct 2019 08:19:48 GMT

Hi Anonymous

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

replying filters by VALUES ( 'Clients'[Region] ) is not necessary, as this filter was not ignored by ALL( 'Clients'[ClientCode] )
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.
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.