Nested Parameters in DAX expression in Table Visual

[dbk58]

Hi. I'm trying to create a table that uses several editable daily rates for several job positions in order to compute total costs. The Positions are different job classes, the levels within positions are the same from a structural point of view, but the rates for the positions vary between the Positions.

I've created fixed decimal parameters for each specific rate, and then tried to use them in an expression like this:

Rate = switch(trim('TimeCard'[Level]),
  "Position1",
   switch(trim('TimeCard'[Classification]),
    "P1_Level1",'P1_Level1_Param_Rate'[P1_Level1_Param_Rate Value],
    "P1_Level2",'P1_Level2_Param_Rate'[P1_Level2_Param_Rate Value],
    "P1_Level3",'P1_Level3_Param_Rate'[P1_Level3_Param_Rate Value],
    "P1_Level4",'P1_Level4_Param_Rate'[P1_Level4_Param_Rate Value],
   ),
  "Position2",
    switch(trim('TimeCard'[Classification]),
     "P2_Level1",'P2_Level1_Param_Rate'[P2_Level1_Param_Rate Value],
     "P2_Level2",'P2_Level2_Param_Rate'[P2_Level2_Param_Rate Value],
     "P2_Level3",'P2_Level3_Param_Rate'[P2_Level3_Param_Rate Value],
     "P2_Level4",'P2_Level4_Param_Rate'[P2_Level4_Param_Rate Value],
   )
)

If I put a simple card on the page and populate it with the What If param value for one of the levels/positions, it updates as expected when I make a change to the parameter. But the values in the table never change. If I try to use this Rate in my table, where I multiply the hours worked by the Rate to compute a total cost, the rate never updates when I change the parameter value. 

The What If parameters are defined like (for Level01) as:

P1_Level01= GENERATESERIES(30, 40, 0.01)

P1_Level01 Rate Value = SELECTEDVALUE('P1_Level1_Param_Rate'[P1_Level1_Param_Rate Value],35)

Is this possible to do? 

[Anonymous · Accepted solution]

Hi @dbk58 ,

Current power bi not support to create dynamic calculated table/column based on filter/slicer.

Measure is a expression for calculate, it works on data view level(visual, filter, measure, slicer) which generated from data model tables and it will been affected by its row contents filter.

They are stored in different data levels and you can't use child level(data view) to affect its parent level(data model: table, column, calculated column/table).

Regards,

Xiaoxin Sheng

[dbk58]

Well that's unfortunate. Essentially these are just constants that get adjusted once per year. 

Oh well - we'll have to look at generating a table with these rates for each position. 

Thanks.