Problem with field parameter and empty slicer

[PBI_VL]

Hello,

I have a field parameter in my report:

Category Switch = {

    ("Age", NAMEOF('Coverages'[AGE_CATEGORY]), 0),

    ("Cover1", NAMEOF('Coverages'[COVER_AGE_CATEGORY]), 1),

    ("Cover2", NAMEOF('Coverages'[COVER_AGE_CATEGORY]), 2),

    ("Cover3", NAMEOF('Coverages'[COVER_AGE_CATEGORY]), 3)

}

This field parameter is used in the Y as in a visual. The X as is clientcount measure.

We have a slicer with options Cover1, Cover2, Cover3 from a dimension table.

When we create the visual when the slicer is empty it works fine. We get the values from column age_category.

When we choose a value in the slicer this also works fine and depending on which cover we choose we get the values from the column cover_age_category. 

But after choosing one cover when we empty the slicer, the visual fails and we get in the Y as all the values from age_category and cover_age_category. But we only want to see the age_category.

Can we change the field parameter to take a blank value for cover into account?

Why does this work when we create the visual with empy slicer, but not when we empty the slicer after choosing one cover?

Thanks in advance!

[v-hashadapu]

Hi @PBI_VL , Thank you for reaching out to the Microsoft Community Forum.

The issue is coming from the way the field parameter is modelled. You are repeating the same field (COVER_AGE_CATEGORY) multiple times in the parameter and then filtering that parameter through DimCover. After you select a Cover and then clear the slicer, Power BI no longer resolves the parameter to a single row, so it behaves like multiple parameter rows are active and shows both AGE_CATEGORY and COVER_AGE_CATEGORY on the axis. That is why the visual works correctly on first load, but not after clearing the slicer.

I would not try to solve this with a “None” or default parameter option, because that is only a workaround. Stop using the field parameter for this logic and instead use a disconnected axis table with a measure that switches between AGE_CATEGORY and COVER_AGE_CATEGORY depending on whether a Cover is selected. That gives you stable behaviour where no Cover selected shows AGE_CATEGORY and selecting a Cover shows COVER_AGE_CATEGORY.

[PBI_VL]

Thank you for the explanation. I understand the problem with the field parameter now.

I created the disconnected table but I'm not sure how to make the measure. How can I select a column depending on the value of the slicer? Can you please give me an example? 

[v-hashadapu · Accepted solution]

Hi @PBI_VL , You cannot dynamically return a column directly from a measure, so use the disconnected table as the axis and then use TREATAS inside the measure to apply the correct column filter. For example, create a disconnected table that contains all category values from both columns, like:
AxisTable =
DISTINCT(
UNION(
SELECTCOLUMNS(Coverages, "Category", Coverages[AGE_CATEGORY]),
SELECTCOLUMNS(Coverages, "Category", Coverages[COVER_AGE_CATEGORY])
)
)

Then use AxisTable[Category] on the Y axis and create a measure like:
ClientCount Dynamic =
VAR CoverSelected =
ISFILTERED(DimCover[Cover])
RETURN
IF(
CoverSelected,
CALCULATE(
[ClientCount],
TREATAS(VALUES(AxisTable[Category]), Coverages[COVER_AGE_CATEGORY])
),
CALCULATE(
[ClientCount],
TREATAS(VALUES(AxisTable[Category]), Coverages[AGE_CATEGORY])
)
)

With this setup, when no Cover is selected the axis behaves like AGE_CATEGORY and when a Cover is selected it behaves like COVER_AGE_CATEGORY. You also avoid the unstable behaviour caused by the field parameter.

[PBI_VL]

Thank you very much for the assistence! It works perfect now!

[Kedar_Pande]

@PBI_VL 

Workaround:

Add a separate "None" option to the parameter.

Use a blank measure or blank column for that option.

Set that as the default state.

[PBI_VL]

How can I set a default state?

I have a table Coverages
For example
clientid/covercd/age/age_category/cover_age_category
1 - Cover1 - 17 - 11-20 - 0-24
2 - Cover1 - 32 - 31-40 - 25-44
3 - Cover1 - 55 - 51-60 - 45-64

I have a table DimCover
For example
Cover1
Cover2
Cover3

I have the field parameter with a relationship between table DimCover and the field parameter.

The slicer is on DimCover.

When nothing is selected on the slicer I want to have the age_category in the Y as.
When a cover is selected on the slicer I want to have the cover_age_category in the Y as.

[Ritaf1983]

Hi @PBI_VL 

This is caused by the Field Parameter behavior rather than by the blank value in the Cover slicer. Microsoft states that when no field is selected in a Field Parameter slicer or filter, Power BI treats it as if all fields are selected. That explains why, after selecting a Cover and then clearing the slicer, the visual ends up showing categories from both AGE_CATEGORY and COVER_AGE_CATEGORY.

I would not try to solve this by adding a blank value to the Field Parameter table, because the parameter itself cannot interpret “no Cover selected” as “use Age only”.

If the required logic is strictly:

no Cover selected → group by AGE_CATEGORY
one Cover selected → group by COVER_AGE_CATEGORY

then a more robust design may be to avoid using a Field Parameter for this axis and instead use a disconnected category table together with a dynamic ClientCount measure that switches its calculation based on whether a Cover is selected.
This approach requires a different setup, but it gives full control over the fallback behavior instead of relying on the Field Parameter’s “no selection = all fields” behavior

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

[PBI_VL]

What should I put in the disconnected category table?

I have a table Coverages
For example
clientid/covercd/age/age_category/cover_age_category
1 - Cover1 - 17 - 11-20 - 0-24
2 - Cover1 - 32 - 31-40 - 25-44
3 - Cover1 - 55 - 51-60 - 45-64

I have a table DimCover
For example
Cover1
Cover2
Cover3

I have the field parameter with a relationship between table DimCover and the field parameter.

The slicer is on DimCover.

When nothing is selected on the slicer I want to have the age_category in the Y as.
When a cover is selected on the slicer I want to have the cover_age_category in the Y as.

[OwenAuger]

Hi @PBI_VL 

It is unusual to have repeated values in the "Fields" column of a field parameter table with different display names in the first column.

With a default field parameter table setup, this causes odd behaviour when using a slicer on the first column of the field parameter table (due to the Group by Columns property that is automatically set).

What is the reason for repeated values of 'Coverages'[COVER_AGE_CATEGORY]?

What behaviour do you expect for each type of selection made on the slicer?

[PBI_VL]

I have a table Coverages
For example
clientid/covercd/age/age_category/cover_age_category
1 - Cover1 - 17 - 11-20 - 0-24
2 - Cover1 - 32 - 31-40 - 25-44
3 - Cover1 - 55 - 51-60 - 45-64

I have a table DimCover
For example
Cover1
Cover2
Cover3

I have the field parameter with a relationship between table DimCover and the field parameter.

The slicer is on DimCover.

When nothing is selected on the slicer I want to have the age_category in the Y as.
When a cover is selected on the slicer I want to have the cover_age_category in the Y as.