cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dvhouten Frequent Visitor
Frequent Visitor

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         Hours

Admin         Tom                 3

Software      Tom                 8

Hardware    Tom                 2

Admin          Sue                 2

Software      Sue                 1

Hardware     Sue                 12

 

I want to include or exclude only "Admin".

 

Thanks,

David

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Including or excluding rows

@dvhouten - 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. 

 

 

View solution in original post

9 REPLIES 9
vmakhija Established Member
Established Member

Re: Including or excluding rows

@dvhouten 

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

Super User
Super User

Re: Including or excluding rows

@dvhouten - 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).

 

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
Nathan Peterson
dvhouten Frequent Visitor
Frequent Visitor

Re: Including or excluding rows

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

Super User
Super User

Re: Including or excluding rows

@dvhouten - 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. 

 

 

View solution in original post

dvhouten Frequent Visitor
Frequent Visitor

Re: Including or excluding rows

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!!!

Super User
Super User

Re: Including or excluding rows

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

 

dvhouten Frequent Visitor
Frequent Visitor

Re: Including or excluding rows

Great breakdown, thanks.

dvhouten Frequent Visitor
Frequent Visitor

Re: Including or excluding rows

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

Super User
Super User

Re: Including or excluding rows

@dvhouten  - 

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 384 members 4,490 guests
Please welcome our newest community members: