Forum Discussion
Help to Format DAX to String/Text Output
Hi, I would like to simplify this dax that outputs a string. It's becoming too complex to maintain and make changes to the output e.g. 1. the format of $1.3M I would like to change to 2 decimal place...
Managed to solve this by tweaking the dax. Although not the refined output I was looking for, but it works for now:)
Budget Slack Message = var _variance2 = ROUND( VALUE( LEFT( SELECTEDVALUE('Last Year Scenario Prev Week'[Var])*100 , 5)),0) var _variance1 = ROUND( VALUE( LEFT( SELECTEDVALUE('Budget Scenario Prev Week'[Var])*100 , 5)),1) return " @here The onsite revenue for " & SELECTEDVALUE('Budget Scenario Prev Week'[FinMonthYearWeek]) & " was USD$" & IF(SELECTEDVALUE('Budget Scenario Prev Week'[onsite revenue])<10000000, LEFT( LEFT( SELECTEDVALUE('Budget Scenario Prev Week'[onsite revenue]),2),1) & "." & RIGHT( LEFT( SELECTEDVALUE('Budget Scenario Prev Week'[onsite revenue]),3),2),LEFT( LEFT( SELECTEDVALUE('Budget Scenario Prev Week'[onsite revenue]),3),2) & "." & RIGHT( LEFT( SELECTEDVALUE('Budget Scenario Prev Week'[onsite revenue]),3),1)) & "M" & " vs budget of USD$" & IF(SELECTEDVALUE('Budget Scenario Prev Week'[Comparative])<10000000, LEFT( LEFT( SELECTEDVALUE('Budget Scenario Prev Week'[Comparative]),2),1) & "." & RIGHT( LEFT( SELECTEDVALUE('Budget Scenario Prev Week'[Comparative]),3),2),LEFT( LEFT( SELECTEDVALUE('Budget Scenario Prev Week'[Comparative]),3),2) & "." & RIGHT( LEFT( SELECTEDVALUE('Budget Scenario Prev Week'[onsite revenue]),3),1)) & "M" & " (" & IF(_variance1 > 0, "+" & _variance1, _variance1) & "%" &")" & " and vs last year at USD$" & IF(SELECTEDVALUE('Last Year Scenario Prev Week'[Comparative])<10000000, LEFT( LEFT( SELECTEDVALUE('Last Year Scenario Prev Week'[Comparative]),2),1) & "." & RIGHT( LEFT( SELECTEDVALUE('Last Year Scenario Prev Week'[Comparative]),3),2),LEFT( LEFT( SELECTEDVALUE('Last Year Scenario Prev Week'[Comparative]),3),2) & "." & RIGHT( LEFT( SELECTEDVALUE('Last Year Scenario Prev Week'[onsite revenue]),3),2)) & "M" & " (" & IF(_variance2 > 0, "+" & _variance2, _variance2) & "%" &")"
Managed to solve this by tweaking the dax. Although not the refined output I was looking for, but it works for now:)
Budget Slack Message =
var _variance2 = ROUND( VALUE( LEFT( SELECTEDVALUE('Last Year Scenario Prev Week'[Var])*100 , 5)),0)
var _variance1 = ROUND( VALUE( LEFT( SELECTEDVALUE('Budget Scenario Prev Week'[Var])*100 , 5)),1)
return
" @here The onsite revenue for " & SELECTEDVALUE('Budget Scenario Prev Week'[FinMonthYearWeek]) & " was USD$" & IF(SELECTEDVALUE('Budget Scenario Prev Week'[onsite revenue])<10000000, LEFT( LEFT( SELECTEDVALUE('Budget Scenario Prev Week'[onsite revenue]),2),1) & "." & RIGHT( LEFT( SELECTEDVALUE('Budget Scenario Prev Week'[onsite revenue]),3),2),LEFT( LEFT( SELECTEDVALUE('Budget Scenario Prev Week'[onsite revenue]),3),2) & "." & RIGHT( LEFT( SELECTEDVALUE('Budget Scenario Prev Week'[onsite revenue]),3),1)) & "M" & " vs budget of USD$" & IF(SELECTEDVALUE('Budget Scenario Prev Week'[Comparative])<10000000, LEFT( LEFT( SELECTEDVALUE('Budget Scenario Prev Week'[Comparative]),2),1) & "." & RIGHT( LEFT( SELECTEDVALUE('Budget Scenario Prev Week'[Comparative]),3),2),LEFT( LEFT( SELECTEDVALUE('Budget Scenario Prev Week'[Comparative]),3),2) & "." & RIGHT( LEFT( SELECTEDVALUE('Budget Scenario Prev Week'[onsite revenue]),3),1)) & "M" & " (" & IF(_variance1 > 0, "+" & _variance1, _variance1) & "%" &")" & " and vs last year at USD$" & IF(SELECTEDVALUE('Last Year Scenario Prev Week'[Comparative])<10000000, LEFT( LEFT( SELECTEDVALUE('Last Year Scenario Prev Week'[Comparative]),2),1) & "." & RIGHT( LEFT( SELECTEDVALUE('Last Year Scenario Prev Week'[Comparative]),3),2),LEFT( LEFT( SELECTEDVALUE('Last Year Scenario Prev Week'[Comparative]),3),2) & "." & RIGHT( LEFT( SELECTEDVALUE('Last Year Scenario Prev Week'[onsite revenue]),3),2)) & "M" & " (" & IF(_variance2 > 0, "+" & _variance2, _variance2) & "%" &")"