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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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...

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

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

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

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

THanks for all your help 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.