Forum Discussion

Imrans123's avatar
Imrans123
Advocate V
4 years ago
Solved

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

  • 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()

    ))