Forum Discussion

Excellove15's avatar
Excellove15
Iron Contributor
Feb 01, 2024

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!
SergeiBaklan 

BI & Data Analysis
Excel
Macros and VBA
office 365
Power BI
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 01, 2024

    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

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

    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

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Feb 01, 2024

    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

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

    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

    • Excellove15's avatar
      Excellove15
      Iron Contributor
      Feb 19, 2024

      Hi SergeiBaklan 

       

      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:

Resources