Ignore specific slicer keeping all other filters

[Ritaf]

Hi all,

I trying to calculate a measure ignoring specific slicer and keeping all other filters and slicers.

I use a dax :

Calculate (count(table1[column1]), all (table1[categoryOnslicer]) .

It is working only if I don't use other filters from the same table.

I need these Filters, how can I handle this problem?

p.s

I cant share the model or create some demo because is huge and complicate model and has real business data inside.

I hope I explained myself clearly enough and this problem has solution.

Thank you, 

Rita

[Anonymous]

I am running into the same issue. Where you ever able to resolve this wihout breaking out the column into a separate dimension table?

I know, this is the "recommended" way, but it is really not fitted for some ways of working with data as explained in Why star schema is set up for failure and in Should auto exist and sort by column implementation be changed?

[amitchandak]

@Ritaf , You have another option of remove filter. But the best option is to move out this column (and related columns) to a new table . and join with the current table on common columns.

Calculate (count(table1[column1]), removefilters(table1[categoryOnslicer])

refer

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

[Ritaf]

Hi,

RemoveFilters behave in the same way....
can you explain to me how can I create another table?

[v-rzhou-msft]

Hi @Ritaf 

I build a sample table to have a test.

Firstly,I build a test measure to found the problem.

test = Calculate (count('Table'[Value]), all ('Table'[User]))

When I remove "e" in User Filter, result in card visual shows 5.

However when I add ID into the Filter and remove 103 in ID filter, the result showing 3 is different from I thought. It should be 4.

Here I have some advice.

1. You can build Slicer tables except categoryOnslicer column.

Here I build an ID table.

ID = VALUES('Table'[ID])

Measure:

Measure1 = 
Var _Selected = ALLSELECTED('ID'[ID])
Return
CALCULATE(COUNT('Table'[Value]),FILTER(ALL('Table'),'Table'[ID]in _Selected))

Result is as below:

Default:

Remove 103 from slicer.

2. Or you can build one measure and add other filters in your measure.

Measure2 = 
VAR _t =
    SUMMARIZE ( ALL( 'Table'), 'Table'[User], 'Table'[ID], 'Table'[Value] )
RETURN
    CALCULATE ( COUNT ( [Value] ), FILTER ( _t, [ID] <>103 ) )

You can add your filters in Filter function.

Result:

You can download the pbix file from this link: Ignore specific slicer keeping all other filters

Best Regards,

Rico Zhou

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

[nilimarodrigues]

This answer helped me to resolve my issue. Scenario was for one of the calculated measure, I wanted to clear out one of the slicer value that user is entering lets say country but at the same time, measure is required to take into account the "region" to which country belongs. What Rico showed for "Measure1" did work for that scenario. Thank you.

[Ritaf · Accepted solution]

Thanks a lot ,

I have already resolved the problem in similiar way,

with "Getting out" the column of slicer to another table.❤️

[pranit828]

HI @Ritaf 

I would try to get the functionality in your ask by using

ALLSELECTED https://docs.microsoft.com/en-us/dax/allselected-function-dax

and modifying it as per your need,

[Ritaf]

Hi, i tried it...has a same behavior 😞

[mahoneypat]

That is weird behavior, as what you have should work like that.  Do you happen to have a Sort By column set up on your category column?  If so, add that column to your ALL too.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Ritaf]

Hi, thank you for responding.
Sorry, i couldn't understand what "All" have with sort and ho can I add it to sort if the sort isn't DAX expression.