topic Re: DAX for conditional formatting for font color in DAX Commands and Tips

DAX for conditional formatting for font color

third_hicana — Mon, 23 Sep 2024 06:10:56 GMT

Hi Everyone. Asking for your help on conditional formatting for font color.

I want to highlight with red color if the 'Value' of each 'money_type' category of projects (those BOP IDs with suffix) and with 'BOP Tagging' equal to Project exceeds the budget of BOP.

For Example

For BOP25-018, it should highlight as red since BOP25-018-B with capex, opex and total of 85, 20.50 , 105.50 respectively exceeds the budget of BOP25-018 with budget 65, 2 and 67 respectively.

While for project BOP25-035-B and BOP25-035-c should NOT be highlighted as red since the totals of these projects does not exceed to the BOP budget of BOP25-035.

I tried this DAX but it also highlights the project budgets that does not exceed to the BOP budget.

VAR projects_tagging = SELECTEDVALUE('Projects Table'[BOP Tagging])

VAR moneyType = {"CAPEX", "OPEX", "TOTAL"}

RETURN

IF(

projects_tagging = "Project" &&

SELECTEDVALUE('Projects Table'[money_type])

IN moneyType

&& 'Projects Table'[Project Refined Budget] > 'Projects Table'[BOP Refined Budget],

"Red"

)

Here' the sample data. Thank you for your help

New BOP ID	money_type	Value	BOP Tagging
BOP25-035	TOTAL	£550.00	BOP
BOP25-035	CAPEX	£350.00	BOP
BOP25-035	OPEX	£200.00	BOP
BOP25-018	TOTAL	£67.00	BOP
BOP25-018	CAPEX	£65.00	BOP
BOP25-018	OPEX	£2.00	BOP
BOP25-035-B	TOTAL	£30.00	Project
BOP25-035-B	CAPEX	£10.00	Project
BOP25-035-B	OPEX	£20.00	Project
BOP25-018-B	TOTAL	£105.50	Project
BOP25-018-B	CAPEX	£85.00	Project
BOP25-018-B	OPEX	£20.50	Project

Re: DAX for conditional formatting for font color

ValtteriN — Mon, 23 Sep 2024 06:57:49 GMT

Hi,

To troubleshoot you should add Projects Table'[Project Refined Budget] and 'Projects Table'[BOP Refined Budget] as variables.

So,

var _BOP_B = SELECTEDVALUE('Projects Table'[BOP Refined Budget])
var _ P_B = SELECTEDVALUE('Projects Table'[Project Refined Budget]).

Now test your filter context like this:
BOP test

VAR projects_tagging = SELECTEDVALUE('Projects Table'[BOP Tagging])
VAR moneyType = {"CAPEX", "OPEX", "TOTAL"}
var _BOP_B = SELECTEDVALUE('Projects Table'[BOP Refined Budget])
var _ P_B = SELECTEDVALUE('Projects Table'[Project Refined Budget]).

RETURN
/*
IF(
  projects_tagging = "Project" &&
  SELECTEDVALUE('Projects Table'[money_type])
  IN moneyType
&& 'Projects Table'[Project Refined Budget] > 'Projects Table'[BOP Refined Budget],
  "Red"
)
*/

_BOP_B

And the same for the other

RETURN
/*
IF(
  projects_tagging = "Project" &&
  SELECTEDVALUE('Projects Table'[money_type])
  IN moneyType
&&
_BOP_B

_P_B

,
"Red"
)
*/

_P_B

This way you can see if your calculation for the budgets is behaving as expected. Now if the values don't mathc your expetations. E.g. if BOP25-035 CAPEX is not 350. You can change the variable's calculation until the result is as expected.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: DAX for conditional formatting for font color

third_hicana — Mon, 23 Sep 2024 07:04:09 GMT

Hi ValtteriN
Thanks for your response. However, the

'Projects Table'[BOP Refined Budget] and 'Projects Table'[Project Refined Budget] are measures in my table. Its does not work in SELECTED VALUE function on my end.

Re: DAX for conditional formatting for font color

ValtteriN — Sun, 29 Sep 2024 10:13:47 GMT

Hi,
In that case you can just test the measures the main point is to see if the measures are returning expected valeus to meet the IF conditions.