CODEWITHSUNDEEP
powerbidax
Markus
Markus

Reputation: 13

Calculate differently depending on lookup value (Dax)

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:

Status

Stuck. Nothing have worked so far.

Upvotes: 1

Views: 290

Answers (1)

bhupendra patel
bhupendra patel

Reputation: 3179

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)

Upvotes: 0

Related Questions