Ignore filter for a measure

[wtdpon]

Hi all,

I have a table visual where I am displaying the following fields for the region of the selected client: product, #sales, ranking in selected client, global ranking. I also have a visual filter so I can filter the table by 'Client' (single selection). This filter comes from the client table where I also have client information: area, market, region. 

E.g: if I select client A, who is from the Madrid area, Spain market and Europe region I would see something like this table that will display product info for the Europe Region:

Product | Sales | Rank in Client | Global Rank

    A          100            3                      2

    C            30            1                      1

    B             15           2                      3

Now, the issue comes when I try to add a second filter for region that I want to use to modigy global rank (so, instead of comparing products from europe to the global, I can choose to compare with America, for example). I want the sales and rank in client to be unaffected by this but when I select a region <> 'Europe' the 'sales' metric becomes blank. I have already tried using all() and allselected() but it's not working. I think the issue comes because the two fields for filtering are at the same table.

My current metric looks like this:

Sales by Region (selected client) =

 CALCULATE(

    SUM('Sales'[Sales CY]),

    ALL('Clients'[Name]),

    FILTER(

    VALUES('Clients'[Region]),

    [Sales CY] <> BLANK() && [Sales CY] > 0

    )

)

Any help is greatly appreciated, thanks!

[wtdpon · Accepted solution]

Hi @v-yiruan-msft many thanks for your reply and apologies for the delay in replying.. I got on with other tasks while this was on hold.

The measure you provided did not work either. However, we finally did it by creating a new table with one column for the distinct regions and having it as a reference for the metric.

Many thanks for your help!

[v-yiruan-msft]

Hi @wtdpon ,

Please update the format of measure as below and check if it can return your expected result...

Sales by Region (selected client) =
CALCULATE (
    SUM ( 'Sales'[Sales CY] ),
    FILTER (
        VALUES ( 'Clients'[Region] ),
        [Sales CY] <> BLANK ()
            && [Sales CY] > 0
    ),
    REMOVEFILTERS ( 'Clients'[Name] )
)

If the above one can't help you, please provide some raw data in your tables 'Clients' and 'Sales' (exclude sensitive data) with Text format and your expected result with backend logic and special examples? By the way, is there any relationship between these two tables? If yes, please provide the related info. It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards