Forum Discussion
Problem with field parameter and empty slicer
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.
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?
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.
Thank you very much for the assistence! It works perfect now!