I have a model with 2 calculation groups: First one to select measured thing (volume, turnover, net sales, count of items is 70+) and second to select "formula" (PY, YTD, budget, rolling 12 months, diff PY, count of items is 61). I have also unit selection table (thousands, millions, per sales volume unit etc, 6 items) what I use to choose correct format string or calculation (eg. user want to see net sales / sales volume not the net sales itself) with formulas.
I have also month index created to my date table. Index 0 newest closed month, 1 is last month and -1 is next month etc. So my plan is use that month index to create conditional format rule like if index >= 0 color is red, if index < 0 color is green. But there are couple of problems
1) Max(day[monthIndex]) gives wrong index when using PY/similar "formulas". This I can hande by using if(selectedvalue(formulaCalcGroup[formula]) in {"PY", "YTD PY"...} index - 12, index) and similar calculations.
2) But this does not work with second calculation group. I think that the problem is that calc group replaces the measure.
3) In some cases I can make a workaround: Replacing dynamic measure with static ones (like taking sum of net sales and does not use the calculation group to select the measured thing) but it does not work with unit table IF i modify the calculation (transform it to per unit). It works fine it I just change the format string (means thousands and millions).
In normal case, everything works well. It might be a little bit slow if you have selected a lot of measured things and a lot of formulas but other than that everything works well. But the conditional formatting does not work at all.
Do we have any solutions, or do I just need to wait that Microsoft implements better conditional formatting with calculation groups?
Extra infos:
My "Dynamic measure" calc group is very simple. All the items are just links to corresponding measures eg. item "Sales volume": It is just [Sales volume]
Formulas calc group is the thought one: There are all the calculations and even "act" is like if(selectedvalue(unit table[unit]) <> per sales volume, selectedmeasure(), divide(selectedmeasure, sales volume])), and all other items are more complicated (most complicated formulas are more than 100 rows of code).
Unit table is used to change format strings eg. : if(selectedvalue(unit table[unit]) = "Thousands", "#,##,", "#,0")
My normal matrix is something like this:
- Rows: Products or Customers
- Columns: some compination with Month, Dynamic measure, formula
- Values: Any measure is ok, I have "bmeasure", which is only blank(). Calculation groups replaces its anyway so you does not need to create anyhing in the measure itself.
