cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sguenther
Advocate II
Advocate II

Ignore Slicer in measure

Hey guys,

 

if I want to ignore a Slicer (on a report page) in a Measure, how would I go about that?

I thought that's when you use the ALL() function like:

 

CALCULATE(
   COUNTROWS(Table1),
   FILTER(ALL(Table1),
      Filter 1 &&
      Filter 2
   )
)

 

But it doesn't seem to work for me. Anything I overlooked?

Thanks!

 

1 ACCEPTED SOLUTION

@mnarmeen For my problem the only thing that works is editing the visual intercations. But in your case, since you are using a date filter, I would recommend a different approach. Always create a date/calendar table and don't link this table to the rest of your data model. Then you can always use this table for your filters and have all the freedom when creating measures, like:

 

Measure =
Calculate(
    Countrows(Table1),
    Filter(Table1,
        Table1[Date] >= MIN(Dateteable[Date]) &&
        Table1[Date] <= MAX(Dateteable[Date]) &&
        other Filter rules....
    )
)

And if you don't want your date filter to influence your measure, you just leave the first two filter rules out.

 

Hope this helps. Let me know if I misunderstood the question.

 

Best,

Sebastian

View solution in original post

11 REPLIES 11
mnarmeen
Helper I
Helper I

Did you manage to solve it? @sguenther , I have the same issue where I have a column in table which have to show overall quantity but the date slicer changes it to the current selection. I want to ignore only one column from filter but it doesnot seems to work. Any idea?

@mnarmeen For my problem the only thing that works is editing the visual intercations. But in your case, since you are using a date filter, I would recommend a different approach. Always create a date/calendar table and don't link this table to the rest of your data model. Then you can always use this table for your filters and have all the freedom when creating measures, like:

 

Measure =
Calculate(
    Countrows(Table1),
    Filter(Table1,
        Table1[Date] >= MIN(Dateteable[Date]) &&
        Table1[Date] <= MAX(Dateteable[Date]) &&
        other Filter rules....
    )
)

And if you don't want your date filter to influence your measure, you just leave the first two filter rules out.

 

Hope this helps. Let me know if I misunderstood the question.

 

Best,

Sebastian

View solution in original post

Hi @SG 

 

Your solution works quite fine for my measures, but now my columns do not respond to the filter anymore since the dates in my table are not linked anymore to the calendar date. How can I fix this?

 

Best,
Anneke

Hi,

@sguenther 

 

It is Working for me as well. Thanks for the solution man. I really appreciate your solution here.

🙂

Thankyou so much. Your recommendation of not linking th date table and using the filter is on point.

I need to make many changes to incorporate this , but you totally nailed it !

Thanks alot, for saving my time and effort !

Anonymous
Not applicable

Hello, 

 

Let's say I can't remove the Filter Table relationship from the Fact Table.  I use the Filter Table as a slicer.  Is it possible to ignore the slicer for measures that count rows in my Fact Table? 

 

Ideally,  I would like to make a selection in the Filter Table, but for the measure in question, count the rows as if the slicer was not selected. 

 

End result, I want to compare a single business unit to total company.  I do not want the other business units visible.  I expect to filter out the other business units by using the business unit slicer but have a measure that calculates total company.  

 

Thank you!

KHorseman
Community Champion
Community Champion

@Anonymousyou would use either the ALL or the ALLEXCEPT formula, depending on the specifics of your case.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Thank you KHorseman! 

 

I was able to figure it out!  I guess I was confussed on how to use the All Except function.  For the two components of the All Except funciton, the table and the column, I thought they had to be in the same table.  I was able to create the following formula.....

 

Calculate( Meas.CountRows , ALLEXCEPT( Fact_Table , Filter_Table[Column] ))

 

Works like a champ!  I'm loving DAX.  

Topher - 

 

I am trying to do the same thing as you I believe but I cannot get it to work! 

 

I would like to create a measure to calculate one market's % to the Total Market. All markets are in one column. I would like my visual to show only the market selected from the visual but still need the measure for my Total Market value to exist even when I filter. 

 

For some reason I can't figure out how to write the code such that I can select one market and still have my Total Market measure be present.

 

Any thoughts?

KHorseman
Community Champion
Community Champion

You can edit interactions with slicers and other filtering elements per-visual. Select a visual and open the Format tab in the ribbon. Turn on Edit Interactions and you can turn off interaction with the slicer in question.

 

There is no way to make a measure ignore a particular slicer. You can make it ignore a column, but it's messy and it will ignore that column everywhere it is referenced, whether it's in a slicer or some other filter. The closest way I know of is to use ALLEXCEPT, which is used to specify every column from that table that you don't want the measure to ignore.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




jahida
Impactful Individual
Impactful Individual

I think the cleanest way to ignore 1 column is ALL('Table'[Column to Ignore]) as one of the filter arguments in the Calculate function. Agreed that Edit Interactions may be preferred in many/most cases.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors