Forum Discussion
FORMAT ignores filter
When I apply the FORMAT function in the following measure, my matrix visual ignores the slicer filter, and displays blank columns.
Here is the measure code:
ERMetricValue =
var sel = SELECTEDVALUE(DimERMetricItem[ERMetric])
var val = SWITCH(sel,
"Preventable Percent", FactMeasure[PreventablePct],
"Avoidable Percent", FactMeasure[AvoidablePct],
SUM(FactEmergency[MetricValue]))
var ret =
SWITCH(
sel,
"Preventable Percent", FORMAT(val, "###%"),
"Avoidable Percent", FORMAT(val, "###%"),
"Allowed Amount", FORMAT(val, "$#,##0"),
"Paid Amount", FORMAT(val, "$#,##0"),
"Visits", FORMAT(val, "#,##0"),
"Avoidable", FORMAT(val, "#,##0"),
"Preventable", FORMAT(val, "#,##0")
)
return ret // using FORMAT - formats correctly, ignores filter
// return val not using FORMAT - filters correctly, no formatting
FORMAT not applied:
Without FORMAT
FORMAT applied:
With FORMAT
Hi Anonymous ,
You could use IF() to hide blank values.
var ret = IF ( val <> BLANK (), SWITCH ( sel, "Preventable Percent", FORMAT ( val, "###%" ), "Avoidable Percent", FORMAT ( val, "###%" ), "Allowed Amount", FORMAT ( val, "$#,##0" ), "Paid Amount", FORMAT ( val, "$#,##0" ), "Visits", FORMAT ( val, "#,##0" ), "Avoidable", FORMAT ( val, "#,##0" ), "Preventable", FORMAT ( val, "#,##0" ) ) )
4 Replies
Hi Anonymous ,
You could use IF() to hide blank values.
var ret = IF ( val <> BLANK (), SWITCH ( sel, "Preventable Percent", FORMAT ( val, "###%" ), "Avoidable Percent", FORMAT ( val, "###%" ), "Allowed Amount", FORMAT ( val, "$#,##0" ), "Paid Amount", FORMAT ( val, "$#,##0" ), "Visits", FORMAT ( val, "#,##0" ), "Avoidable", FORMAT ( val, "#,##0" ), "Preventable", FORMAT ( val, "#,##0" ) ) )Anonymous , I am not able to notice where it missed filter.
Format will make this column as text and it will behave like text
