Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 :
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
Solved! Go to 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 )
Kind regards,
Lucas
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:
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:
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?
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 😞
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.
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 )
Kind regards,
Lucas
Ah! good to hear that! Sorry I was confused from the beginning…
THanks for all your help 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
13 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
25 | |
3 | |
2 | |
2 | |
2 |