Forum Discussion
Removing Nulls from Only One Parameter Field
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?
Hi Anonymous
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
3 Replies
- Ritaf1983
Super User
Hi Anonymous
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/1447523Ritaf1983 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
Hi Anonymous
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