Measure Shows Blank Values When Slicer Should Remove Lines

[Anonymous]

Hello, 

I'm having trouble with a measure showing values in a table when I expect the slicer to remove the line in the table. Instead, when I select the slicer value, I was hoping the entire row would be removed from the visual table, and I don't know how. 

To set up an example, I have a data table that has people and the projects they're working on like so: 

Person	Project
Person1	ProjectA
Person1	ProjectB
Person2	ProjectC
Person2	ProjectD
Person2	ProjectE

I wanted to show a visual table in the report that demonstrates bandwidth. So, I made a measure. My measure calculates bandwidth based on project count. So, when the project count = 2, the person is "Properly Loaded." When the count is 3, they are "Overloaded."

My resulting visual table with the measure looks like this: 

Person	Project Count	Bandwidth
Person1	2	Properly Loaded
Person2	3	Overloaded

I wanted to add another data table that mapped Team to the person so that Teams could view just their own team when looking at the report, so I made another data table that mapped like so: 

Person	Team
Person1	Team Red
Person2	Team Blue

This is connected to the original data table by Person, with this Team mapping table having a 1 to many relationship and filtering the projects table. 

When I made a slicer that just shows the Team Name (Red, Blue) and select one team, the other person still shows in the table. So, if I select Red, this is what shows in the visual table: 

Person	Count	Bandwidth
Person1	2	Properly Loaded
Person2		Overloaded

I would think that selecting Team Red would remove the line for Person 2 entirely, but they still show. Just the count is removed, but not the measure. 

How can I design the measure to remove those rows? 

Here's the measure code I used for my real data: 

CALCULATE(IF(COUNT('All Team Member Projects'[Task]) = 4, "Slight Overload", IF(COUNT('All Team Member Projects'[Task]) >= 5, "Overloaded", IF(OR(COUNT('All Team Member Projects'[Task]) = 2, COUNT('All Team Member Projects'[Task]) = 3), "Proper Loading", "Available"))))

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try to create measure with below dax formula:

Count = COUNTROWS('Table')

Bandwidth =
VAR _a = [Count]
VAR _result =
    SWITCH (
        _a,
        2, "Properly Loaded",
        3, "Properly Loaded",
        4, "Slight Overload",
        5, "Overloaded"
    )
RETURN
    _result

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hey, this is great and it totally works. 

Could I bother you for a more detailed explanation of how this works compared to my original attempt?