Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Including or excluding rows

What is the best method for including/excluding rows that contain a certain value, filter or slicer?  And how would that look?  Here is some sample data. TASKTABLE Task             Resource        ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 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's avatar
Anonymous
Not applicable
7 years ago

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

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Great breakdown, thanks.