Search code examples
parameterspowerbi

Power BI Field Parameter - how to dynamically exclude nulls

Team --

I have a Power BI report which leverages field parameters to allow users to toggle between different demographic categories (age, education, income, etc). If someone selects 'education category' that's what they see ... if they toggle to 'age category', the charts will change automatically. Screen shot below: enter image description here

The issue is that in the example below, it still have the 'N/A' values include ... if I use the report filter to exclude education category = 'N/A' then this chart appears correct. However when the users toggles to another field parameter (age category for example), the education category ='N/A' would still be excluded even if they have a value for age category.

So the question is: how (if possible) do I set the field parameters to have conditions as well? So if the end user selects 'age category' it will show all records as long as there's a value in age category. And if the user selects 'education category', it will show all records as long as here's a value in education category and the chart is then incorrect.

If I try to exclude all nulls / N/A values from all the fields, I would only be left with records that contain information in all those fields which is also incorrect.

Dynamic Axis = {
    ("Age Category", NAMEOF('Consumer_Demographics'[Age Category]), 0),
    ("Income Category", NAMEOF('Consumer_Demographics'[Income Category]), 1),
    ("Education Category", NAMEOF('Consumer_Demographics'[Education Category]), 2),
    ("Presence Children", NAMEOF('Consumer_Demographics'[Presence Children]), 3),
    ("Presence Teens", NAMEOF('Consumer_Demographics'[Presence Teens]), 4),
    ("County Pop", NAMEOF('Consumer_Demographics'[County Pop]), 5),
    ("Relation Status", NAMEOF('Consumer_Demographics'[Relation Status]), 6)
}
Solution

  • After several attempts, the trick was to create a new measure and use that in conjunction with the parameter.

    So the new measure is:

    Report_Measurement = 
IF(ISINSCOPE('Consumer_Demographics'[Age Category]),calculate(sum('Consumer_Demographics'[measurement]),filter('Consumer_Demographics','Consumer_Demographics'[Age Category]<>blank())),
IF(ISINSCOPE('Consumer_Demographics'[Education Category]),calculate(sum('Consumer_Demographics'[measurement]),filter('Consumer_Demographics','Consumer_Demographics'[Education Category]<>blank())),
IF(ISINSCOPE('Consumer_Demographics'[Income Category]),calculate(sum('Consumer_Demographics'[measurement]),filter('Consumer_Demographics','Consumer_Demographics'[Income Category]<>blank())),
IF(ISINSCOPE('Consumer_Demographics'[Presence Children]),calculate(sum('Consumer_Demographics'[measurement]),filter('Consumer_Demographics','Consumer_Demographics'[Presence Children]<>blank())),
IF(ISINSCOPE('Consumer_Demographics'[Presence Teens]),calculate(sum('Consumer_Demographics'[measurement]),filter('Consumer_Demographics','Consumer_Demographics'[Presence Teens]<>blank())),
IF(ISINSCOPE('Consumer_Demographics'[County Pop]),calculate(sum('Consumer_Demographics'[measurement]),filter('Consumer_Demographics','Consumer_Demographics'[County Pop]<>blank())),
IF(ISINSCOPE('Consumer_Demographics'[Relation Status]),calculate(sum('Consumer_Demographics'[measurement]),filter('Consumer_Demographics','Consumer_Demographics'[Relation Status]<>blank()))
,blank())))))))

    And then the pie chart visualization uses this new measure and the parameter I had already created. enter image description here

    I also had to revisit the backend SQL to ensure that blanks were flowing into the fields instead of a placeholder value.