Removing Nulls from Only One Parameter Field

[KHeddens]

Hello,

I have the following code for a Field Parameter I created in Power BI: 

Production Role = {
("Product Engineer", NAMEOF('Assignment'[Product Engineer]), 0),
("Intern", NAMEOF('Assignment'[Intern]), 1),
("Product Developer", NAMEOF('Assignment'[Product Developer]), 2)
}

On the visual side of things, I have a table created with information based off of these three Production Roles, and have it set up so that you can click between the three Production Roles and it will update the table and cards I have on the front end based off of that specific role. The issue I am having is that I don't want null names to show up for Intern because the blank names will influence the totals for different metrics I have set up in the table and cards (due to some assignments having a Product Developer or Product Engineer but no Intern. Every assignment has both a Product Developer and Engineer, but it is much more rare to have an Intern). So I want to not show the information for null Interns, but ONLY if the Production Role is Intern. Otherwise, I want the nulls to stay so that if there is an Product Developer or Product Engineer on an Assignment but not an Intern, those Assignments that don't have an Intern will still show up when I click on either the Product Developer or Product Engineer and give them their correct metric numbers on the table and cards. Any ideas?

[Ritaf1983]

Hi @KHeddens 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[KHeddens]

@Ritaf1983   Here is an example of the table named "Assignment."

Assignment	Product Engineer	Product Developer	Intern	Total Produced	Total Sold
232451	Mannie Jacobson	Carlie Abrams		341	238
232452	Jerrie Vinland	Jose Martinez		121	99
232453	Connie Bandit	Mickie Barrett	Mariah Ham	645	561
232454	Mannie Jacobson	Jose Martinez		222	106
232455	Connie Bandit	Carlie Abrams		99	88
232456	Mannie Jacobson	Carlie Abrams	Mariah Ham	561	498

Here is what the Field Parameter looks like, a standard field parameter based off of fields in the assignment table.

Production Role	Parameter Fields	Parameter Order
Product Engineer	'Assignment'[Product Engineer]	0
Product Developer	'Assignment'[Product Developer]	1
Intern	'Assignment'[Intern]	2

Now, this is what the tables look like on the front end, imagining that each table pops up when you click on the role tile based on the parameter, and the first row in the table is the role type.

Product Engineer	Total Produced	Total Sold
Mannie Jacobson	1124	842
Connie Bandit	744	649
Jerrie Vinland	121	99
TOTAL	1989	1590

Product Developer	Total Produced	Total Sold
Carlie Abrams	1001	824
Jose Martinez	343	205
Mickie Barrett	645	561
TOTAL	1989	1590

Intern	Total Produced	Total Sold
	783	531
Mariah Ham	1206	1059
TOTAL	1989	1590

So here you can see that nulls are pulling in for Interns, even though I only want the TRUE intern totals. The other two charts are perfectly fine as there are no nulls and nothing is skewing the data, so we want to make sure the assignments that have interns that are null are still pulling product developers and engineers so that their totals are correct. But imagine if there were many many more rows and more interns, they could see their personal totals, but managers would not be able to see the production of interns as a whole. So I want to be able to have this parameter set up where you can click on a slicer role tile and switch between the three roles to see the three charts I have put aboce, but only have to make the one chart thanks to parameters. But I want to take out null role names so that the totals are correct.

[Ritaf1983 · Accepted solution]

Hi @KHeddens 
To achieve a goal you can take these few steps :

1 With power query replace "blanks " with null (until you do it the engine of pbi does not recognize these cells as blanks.

2. After creating field parameters add to the parameters table another column with the parameter name to have an ability to create conditions with it 

3 . Create a flag measure that will check which parameter was selected and if it is blank:

test_intern = if(SELECTEDVALUE('Parameter'[Test parametr])="Intern" && ISBLANK(max('Table'[Intern])),1,0)

4. Use this measure to filter the visual

pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly