Forum Discussion

sivarajan21's avatar
sivarajan21
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

Reference the field parameter in dax measures to dynamically change the visuals

Hi,

 

My requirement is to use columns from field parameter to dynamically change the visual
Field parameter table is as below:

The columns Category, Category Main are calculated columns that are used in slicer buttons in visual.

 

Now what I want to achieve is, when I click the Target button, and Units or cost I should have corresponding values dynamically selected in the below visual. Similarly when i select the Budget Cost/Units it should change the visual. 

 

The line Y-axis of the above visual works perfectly as brought in the Parameter directly. But for column y-axis I have two things to be taken care of 
i)Actual

ii)Forecast

 

I had tried to tweak the forecast measure but its not dynamically changing the values, The forecast measure consist of 2 measures (preforecast & forecast)

Pre_forecast =
VAR _Totalunits = CALCULATE(SUMX(Target,Target[Value]),FILTER('Parameter 2','Parameter 2'[Type] = MAX('Parameter 2'[Type])))
VAR _noofdays = CALCULATE(
DATEDIFF(
    EOMONTH(MAX(Calendar_[Date]),-1),
    EOMONTH(MAX(Calendar_[Date]),0),
    DAY
    )
)
VAR _DailyUnits = DIVIDE(_Totalunits,_noofdays)
VAR _replaceblank = IF(
    ISBLANK([Actual Units]),
                _DailyUnits*_noofdays,IF([Actual Units] = 0,
                BLANK()))
RETURN _replaceblank
---------
Forecast Target Unit = IF (

    HASONEVALUE ( 'Calendar_'[Month] ),

    CALCULATE ( SUMX ( VALUES ( Points[DBName-Point_Id] ), [Pre_forecast_1] ) ),

    SUMX (

        VALUES ( 'Calendar_'[Month] ),

        CALCULATE ( SUMX ( VALUES ( Points[DBName-Point_Id] ), [Pre_forecast_1] ) )

    ))
Pre_forecast is the main measure that i am trying to reference the parameter.
 
Could you please help me to achieve this?
PFA file in link here Portfolio 6 latest.pbix

 

Thanks in advance!
Ahmedx amitchandak Greg_Deckler Ashish_Mathur Anonymous marcorusso 

2 Replies