Calculate differently depending on lookup value (Dax)

1
votes

Current setup

PowerPivot 2010.

Date dimension table with [Date] as key field and [Type of measure], [Runrate base] as mapping fields. The values in [Type of measure] are either 'Actuals' or 'Budget' and the values in [Runrate base] are either 'X' or null.

Cost fact table

Calculated metric [Runrate] that are to either a) summarize values from cost fact table for dates which are mapped as 'Actual' or b) calculate average from cost fact table for dates which are tagged with 'X' in [Runrate base] field

Problem

Formula for measure [Runrate] for calculating according to a) and according to b) are OK.

Formula for measure [Runrate] for deciding WHETHER to do a) or b) is the problem.

Attempted approaches

Have attempted to do:

  • If [Type of measure] = "Actuals" then a) else b)
  • Use supporting measures such as [Actuals] with filtering [Type of measure] = 'Actuals' and then in measure [Runrate] have a 'if then else' statement

Status

Stuck. Nothing have worked so far.

1
powerbidax
What are the exact DAX formulas that you tried and how did they not work (what error did they give or what result did they return)? Some sample input and desired output would also be useful for those trying to answer. – Alexis Olson

1 Answers

0
votes

Since there is lack of input and desired output as Alexis has suggested here is an answer based on assumption

  • That you are looking to dynamically switch measure value on report

  • You already have the actual measures you want to display

    1. Create a parameter table and put the values Actual & Budget
    2. Use the table created above to create filter which will allow you to switch between Actual & Budget
    3. Create a Measure with IF(HASONEVALUE(tblParam[MeasureName]),SWITCH(VALUES(tblParam[MeasureName]),"Actual",ActualValue,"Budget",BudgetValue), usesomedefaultvalue)