Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

14 REPLIES 14
QuantumMatrix
Advocate I
Advocate I

Thank you so much!
SOLUTION:
CALCULATE( COUNTROWS(yourtable), ALL())

You don't even have to pass any arguments to the ALL() function.
Nothing will filter, unless you add it to the measure.

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

Anonymous
Not applicable

I was stuck for ages with this, I couldnt think a simple trick like this would do it. Thank you so much

Anonymous
Not applicable

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

Anonymous
Not applicable

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!

@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!




I just have to say... We have an absurdly complicated report page that was built to specifications based on our CEO's request and we had one measure that was absolutely plaguing us because of how we have to apply slicers to the content.

THIS solution ended up being the answer to our problem. DAX is amazing because the solution is often surprisingly simple, just have to expand our familiarity with DAX and all the potential it holds.

Thank you!

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.