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
6 years ago

Nathan,

One last question, since "Include Admin" is in the "Rows" section, the matrix sees it as one more level to drill down, but when you do, you get the "Can't determine the relationship" error.  Is there a way to stop the drilldown before it hits that level?

Thanks,

David

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous  - 

    I don't think you can - I was picturing "Include Admin" as a separate Slicer visual.