Forum Discussion
Including or excluding rows
Anonymous - Sorry, should be SUM(TASKTABLE[Hours]), so the measure should be:
Hours Measure = var _inclusive = IF( HASONEVALUE(Parameters[Include Admin]), IF( VALUES(Parameters[Include Admin]) = "Yes", "Inclusive", "Exclusive" ), "Inclusive" ) return CALCULATE( SUM(TASKTABLE[Hours]), FILTER( 'TASKTABLE', OR( _inclusive = "Inclusive", [Task] <> "Admin" ) ) )And then use this measure in the visual filter.
Anonymous
if you want your report users to not see "admin" rows at all, then it will be best to exclude them in Power Query.
On the other hand, if you want users to show / hide "admin" rows, then slicer or page level filter (along with filter pane) will be ideal.
Regards
That did it. Now I need to analyze what I just did so I will understand the "why". ;-) I will need it again in the very near future. Mostly what I just learned is that I have a LOT more to learn.
THANKS!!!
You're welcome. I corrected the original answer now, so that it won't confuse future viewers.
Major points to understand:
1. When you want to use Include/Exclude scenarios like the one you specified, you want to use a Disconnected (unrelated) Parameters table.
-This table can be used to specify HOW filters are applied to a measure, instead of using a normal filter.
-The crossjoin in the parameters table is used to make sure different columns within that table don't affect each other.
2. The _inclusive variable can be used in any Include/Exclude scenario. There are 3 possibilities:
-HASONEVALUE = FALSE. In this scenario, you need to decide whether you will include/exclude by default.
-Yes = Include Everything
-No = Exclude Something
3. The OR filter parameter makes sure that everything is included if "Inclusive", and makes sure that things that should always be included are included (<>Admin).
Hope this helps on your learning journey!
Nathan
Great breakdown, thanks.