Forum Discussion
Including or excluding rows
- Anonymous7 years ago
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 - For the simplest cases, filter vs slicer is merely a matter of preference. Some thoughts:
Arguments for Filters:
1. Filter Pane can used as the "one-stop-shop" to check the current scope.
2. Filters can be applied to a visual, page, or report in a consistent way.
3. Filters have options for advanced filtering.
4. Filters take up less space (if the filter pane is showing anyway)
Arguments for Slicers:
1. Slicer has more flexible formatting.
2. Slicer can be linked across pages with more flexibilty.
3. Slicers give more of a "feeling of being part of the report".
4. Slicer can never be hidden, as the FIlter Pane can. Therefore, the scope is not hidden.
How it would work:
If you are interested in always showing the other values, and sometimes showing admin, try the following:
1. Add a Disconnected Parameters table, like this (I included one other parameter to show how you can combine multiple parameters into this table.):
Parameters =
var budg_act = DATATABLE("Budget vs Actuals Display",STRING,{{"Actuals Only"},{"Both"},{"Budget Only"}})
var inc_admin = DATATABLE("Include Admin",STRING,{{"No"},{"Yes"}})
return CROSSJOIN(budg_act,inc_admin)
2. Add a measure like the following:
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"
)
)
)
3. Add a filter or slicer based on "Include Admin" column.
4. Add a filter to your visual based on "Included Row" measure (>0).
- Anonymous7 years agoNot applicable
Thanks for the explanation and examples, I'm a lot further than I was. :-)
It's not working, but I don't understand the "CALCULATE(SUM(Parameter[One]),". I changed it to:
CALCULATE(DISTINCT(Parameter[Include Admin]),, but I'm not sure what to put in the visual filter.
Thanks,
David
- Anonymous7 years agoNot applicable
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.