cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lvmoraes
Helper I
Helper I

Ignore a Slicer in a object, only for the measure

Hi,

 

I would like to create a measure that ignores a field filter ( Slicer ), like year or a flag for example.

 

I have this measure:

 

SeasonOnly = CALCULATE( Sum( Sales[UnitsSold] ) , FILTER( All( Sales[IsSeason] ), Sales[IsSeason] = 1 ))

 

Which is quite close to what I need. But when I apply a slicer, this is what happens :

 

0.PNG1.PNG2.PNG

 

As you can see, when I select 0 in the Slicer, the product Belt will disapear, as its unitsSold with IsSeason = 0 is null. However, the SeasonOnly measure should be there in the table with 56,

 

Is there anyway to make this work in this context?

 

Not easy to explain, hope my example could make it clear.

 

Thanks

Lucas

 

1 ACCEPTED SOLUTION

For future references , I could sort it.

That is what I was looking for ( select 0 at the slicer , expression filtering 1 didn't change at all )

 

sol.PNG

 

Kind regards,

Lucas

View solution in original post

11 REPLIES 11
dilumd
Solution Supplier
Solution Supplier

Try this,

IsSeasons = CALCULATE(SUM(Sales[UnitSold]),Sales[IsSeason]=1) 

if you can share some sample data, I can see what's exactly happening. since you are filtering out only (Sales[IsSeason] = 1) in your calculate function it ignores all other filters and calculate quantity for season = 1.

Thanks Dilumd,

 

Your expression works, but when I select IsFilter = 0 , I have the wrong behaviour. Let me try to explain again, with a better picture. So this is my data:

 

4.PNG

 

I want a expression that, sum only data for season = 1, So far, your measure is ok. But if I select the slice = 0, then Belt should still show 56 at the measure , as this is the value for season 1. Instead, it will remove it from the table:

 

5.PNG

 

Basically I want to ignore the Slicer for both dimensions and measures ( or simply override it ), because that is being explicitly declared at the measure.

 

Thank you, please let me know if I wasn't clear. The data is that little table...

dilumd
Solution Supplier
Solution Supplier

Hi,

 

Please tell me whether your original data set looks something like this?

 

SS.JPG

Hi dilumd,

 

thanks  for your reply.

 

Yes, that would be pretty much it

dilumd
Solution Supplier
Solution Supplier

Hi,

 

Please check the attached pbix file below. I think your file may not have a value for “belt and shirts”.

 

https://www.dropbox.com/s/w76hu8snccjuqxs/Help.pbix?dl=0

 

 

Thank you dilumb, you are so right, this is what I have in my database ( isSeason 0 filtered 😞

 

6.PNG

 

But the expression should show the products where IsSeason = 1, overriding the Slicer... basically removing the lines with UnitSold = 0 from your database, and still having the same results.

 

Do you think it is possible to create such expression?   ( that completely overrides the slider )

 

THanks

Lucas

dilumd
Solution Supplier
Solution Supplier

Hi,

 

Not sure I understood your requirement well. However, try below functions, depending on what you are going to show in your table one of the below functions may serve your purpose. refer the pbix file https://www.dropbox.com/s/w76hu8snccjuqxs/Help.pbix?dl=0

 

IsSeasons1 = CALCULATE(SUM(Help[UnitSold]),ALL(Help[IsSeason]))
IsSeasons = CALCULATE(SUM(Help[UnitSold]),ALLSELECTED(Help[IsSeason]))

 

Thanks for all your time and patience on this, but I think maybe I am not explaining well...

 

 Please think about the following scenario :

1 - remove tha last record from your data ( Shirts 0 )

2 - the measure should return 187 no matter if 0 or 1 is selected on the slicer.

 

8.png

 

 

Thanks

Lucas

 

For future references , I could sort it.

That is what I was looking for ( select 0 at the slicer , expression filtering 1 didn't change at all )

 

sol.PNG

 

Kind regards,

Lucas

dilumd
Solution Supplier
Solution Supplier

Ah! good to hear that! Sorry I was confused from the beginning… 

THanks for all your help 🙂

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors