SOLVED

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

Brass Contributor

Hi,

 

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

Excellove15_2-1706770669720.png

 

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

Excellove15_1-1706770621541.png

 

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. 

Excellove15_0-1706770594942.png

 

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!
@Sergei Baklan 

4 Replies

@Excellove15 

I didn't dig so far, some later. As an idea you may use calculation group to substitute measures used in columns. Details are here Using calculation groups to selectively replace measures in DAX expressions - SQLBI

best response confirmed by Excellove15 (Brass Contributor)
Solution

@Excellove15 

If with fields parameter - I'd create two parameters tables, one for the measures shown in lines, and another one for the measures shown in columns.

On the top two simple calculated tables, for the Category and Category Main used for slicers. Like this

image.png

With that you may slice both columns and lines with the same slicers.

image.png

I simply duplicated Parameter 2 table into Parameter 3 one. Thus the same measure in line and columns and blanks are not cleaned.

File is here Portfolio 6 latest.pbix

Hi @Sergei Baklan 

 

Apologise for the delay, & thanks for your quick response!

This really worked like a wonder and thanks for the awesome solution:smile:

You always come to my rescue and thanks once again!

 

Please let me know if i can do anything for you:smile:

1 best response

Accepted Solutions
best response confirmed by Excellove15 (Brass Contributor)
Solution

@Excellove15 

If with fields parameter - I'd create two parameters tables, one for the measures shown in lines, and another one for the measures shown in columns.

On the top two simple calculated tables, for the Category and Category Main used for slicers. Like this

image.png

With that you may slice both columns and lines with the same slicers.

image.png

I simply duplicated Parameter 2 table into Parameter 3 one. Thus the same measure in line and columns and blanks are not cleaned.

File is here Portfolio 6 latest.pbix

View solution in original post