Forum Discussion
DAX Conditional Formatting, return blank icon
Was wondering if anyone could help.
I have a table with rows of metrics, and for each metric I have a Current year measure and a previous year measure.
I have a matrix where the rows are metric name and for each row, I want to conditionally format based on whether current year is greater than previous year. Take the below example for reference,
IF(
HASONEVALUE('Metric'[Metric Name])
SWITCH (
VALUES ('Metric'[Metric Name]),
"Sales", IF([Sales]>[Sales PY], "TriangleHigh","TriangleLow"),
"Expenses",IF([Expenses]<[Expenses PY], "TriangleHigh","TriangleLow"),
"Profit",IF([Profit]>[Profit PY], "TriangleHigh","TriangleLow"),
"TriangleMedium"
))
This works exactly the way I want it to. However, if I have a metric I did not list in the above measure but is in the metric name column, for that metric, it shows the triangle medium icon.
I would like to make it such that if a metric not mentioned above is present, then to just not show any icon whatsoever. I tried changing the measure to
IF(
HASONEVALUE('Metric'[Metric Name])
SWITCH (
VALUES ('Metric'[Metric Name]),
"Sales", IF([Sales]>[Sales PY], "TriangleHigh","TriangleLow"),
"Expenses",IF([Expenses]<[Expenses PY], "TriangleHigh","TriangleLow"),
"Profit",IF([Profit]>[Profit PY], "TriangleHigh","TriangleLow"),
""
))
However, it just shows a broken image link for the metrics not mentioned.
Imrans123 , try blank
IF(
HASONEVALUE('Metric'[Metric Name])
SWITCH (
VALUES ('Metric'[Metric Name]),
"Sales", IF([Sales]>[Sales PY], "TriangleHigh","TriangleLow"),
"Expenses",IF([Expenses]<[Expenses PY], "TriangleHigh","TriangleLow"),
"Profit",IF([Profit]>[Profit PY], "TriangleHigh","TriangleLow"),
blank()
))
2 Replies
- amitchandakSuper User
Imrans123 , try blank
IF(
HASONEVALUE('Metric'[Metric Name])
SWITCH (
VALUES ('Metric'[Metric Name]),
"Sales", IF([Sales]>[Sales PY], "TriangleHigh","TriangleLow"),
"Expenses",IF([Expenses]<[Expenses PY], "TriangleHigh","TriangleLow"),
"Profit",IF([Profit]>[Profit PY], "TriangleHigh","TriangleLow"),
blank()
))
- Imrans123Advocate V
Perfect! Thanks!