Power BI - Need Help Optimizing Dynamic Measure Selection in Power BI Parameter Table

[NagaPushpa]

Hi everyone,

I'm working on a Power BI report where I need to display utilization trends dynamically across years, quarters, and months in matrix visual and bar chart. I have created:

• 3 measures for dynamic year selection
• 12 measures for all quarters from the past 3 years
• 36 measures for all months from the past 3 years

  I added all these measures into a parameter table using the following structure:
  
  

  TrendsParameter =
  {
  -- Yearly Data
  (FORMAT(YEAR(TODAY()) - 2, "0000"), NAMEOF('ParameterTable'[YearlyTrend_Prev2Years]), 0),
  (FORMAT(YEAR(TODAY()) - 1, "0000"), NAMEOF('ParameterTable'[YearlyTrend_Prev1Year]), 1),
  (FORMAT(YEAR(TODAY()), "0000"), NAMEOF('ParameterTable'[YearlyTrend_ThisYear]), 2),

  -- Quarterly Data
  ("Q1 " & FORMAT(YEAR(TODAY()) - 2, "0000"), NAMEOF('ParameterTable'[QuarterlyTrend_Prev2Years_Q1]), 3),
  ("Q2 " & FORMAT(YEAR(TODAY()) - 2, "0000"), NAMEOF('ParameterTable'[QuarterlyTrend_Prev2Years_Q2]), 4),
  ("Q3 " & FORMAT(YEAR(TODAY()) - 2, "0000"), NAMEOF('ParameterTable'[QuarterlyTrend_Prev2Years_Q3]), 5),
  ("Q4 " & FORMAT(YEAR(TODAY()) - 2, "0000"), NAMEOF('ParameterTable'[QuarterlyTrend_Prev2Years_Q4]), 6),

  ("Q1 " & FORMAT(YEAR(TODAY()) - 1, "0000"), NAMEOF('ParameterTable'[QuarterlyTrend_Prev1Year_Q1]), 7),
  ("Q2 " & FORMAT(YEAR(TODAY()) - 1, "0000"), NAMEOF('ParameterTable'[QuarterlyTrend_Prev1Year_Q2]), 8),
  ("Q3 " & FORMAT(YEAR(TODAY()) - 1, "0000"), NAMEOF('ParameterTable'[QuarterlyTrend_Prev1Year_Q3]), 9),
  ("Q4 " & FORMAT(YEAR(TODAY()) - 1, "0000"), NAMEOF('ParameterTable'[QuarterlyTrend_Prev1Year_Q4]), 10),

  ("Q1 " & FORMAT(YEAR(TODAY()), "0000"), NAMEOF('ParameterTable'[QuarterlyTrend_ThisYear_Q1]), 11),
  ("Q2 " & FORMAT(YEAR(TODAY()), "0000"), NAMEOF('ParameterTable'[QuarterlyTrend_ThisYear_Q2]), 12),
  ("Q3 " & FORMAT(YEAR(TODAY()), "0000"), NAMEOF('ParameterTable'[QuarterlyTrend_ThisYear_Q3]), 13),
  ("Q4 " & FORMAT(YEAR(TODAY()), "0000"), NAMEOF('ParameterTable'[QuarterlyTrend_ThisYear_Q4]), 14),

  -- Monthly Data
  ("Jan " & FORMAT(YEAR(TODAY()) - 2, "0000"), NAMEOF('ParameterTable'[MonthlyTrend_Prev2Years_M1]), 15),
  ("Feb " & FORMAT(YEAR(TODAY()) - 2, "0000"), NAMEOF('ParameterTable'[MonthlyTrend_Prev2Years_M2]), 16),
  ("Mar " & FORMAT(YEAR(TODAY()) - 2, "0000"), NAMEOF('ParameterTable'[MonthlyTrend_Prev2Years_M3]), 17),
  ("Apr " & FORMAT(YEAR(TODAY()) - 2, "0000"), NAMEOF('ParameterTable'[MonthlyTrend_Prev2Years_M4]), 18),
  ("May " & FORMAT(YEAR(TODAY()) - 2, "0000"), NAMEOF('ParameterTable'[MonthlyTrend_Prev2Years_M5]), 19),
  ("Jun " & FORMAT(YEAR(TODAY()) - 2, "0000"), NAMEOF('ParameterTable'[MonthlyTrend_Prev2Years_M6]), 20),

  ("Jan " & FORMAT(YEAR(TODAY()) - 1, "0000"), NAMEOF('ParameterTable'[MonthlyTrend_Prev1Year_M1]), 21),
  ("Feb " & FORMAT(YEAR(TODAY()) - 1, "0000"), NAMEOF('ParameterTable'[MonthlyTrend_Prev1Year_M2]), 22),
  ("Mar " & FORMAT(YEAR(TODAY()) - 1, "0000"), NAMEOF('ParameterTable'[MonthlyTrend_Prev1Year_M3]), 23),

  ("Jan " & FORMAT(YEAR(TODAY()), "0000"), NAMEOF('ParameterTable'[MonthlyTrend_ThisYear_M1]), 24),
  ("Feb " & FORMAT(YEAR(TODAY()), "0000"), NAMEOF('ParameterTable'[MonthlyTrend_ThisYear_M2]), 25),
  ("Mar " & FORMAT(YEAR(TODAY()), "0000"), NAMEOF('ParameterTable'[MonthlyTrend_ThisYear_M3]), 26)
  }

  In the same parameter table, I created a calculated column as follows:
  ViewSelectionCategory =
  

  IF(

      'ParameterTable'[Order] < 3,

      "Yearly",

      IF(

          'ParameterTable'[Order] >= 3 && 'ParameterTable'[Order] < 15,

          "Quarterly",

          IF(

              'ParameterTable'[Order] >= 15 && 'ParameterTable'[Order] < 42,

              "Monthly",

              "Other"

          )

      )

  )Goal is to:
  

  • I want to dynamically switch between Yearly, Quarterly, and Monthly trends using a slicer.
  • The slicer (added ViewSelectionCategory in it) should control which measure is displayed based on user selection.
  • The approach should be efficient in terms of performance.
  • There are more number of measure added only for this requirement, need to reduce them with having the same functionality.

    Challenge:

    • Is there a better way to structure this parameter table to improve performance and maintainability?
    • Would an alternative approach (e.g., using a disconnected table or aggregating differently) work more efficiently?
    • Any recommendations on handling dynamic selections for year, quarter, and month more effectively?

      Appreciate any insights or best practices! 

      Thanks in advance! 

[v-pgoloju]

Hi @NagaPushpa,

Just a gentle reminder — has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

[v-pgoloju]

Hi @NagaPushpa,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution — it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

[v-pgoloju]

Hi @NagaPushpa,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.

Best regards,

Prasanna Kumar

[bhanu_gautam]

@NagaPushpa Create a Disconnected Parameter Table
Create a table with the possible selections for Yearly, Quarterly, and Monthly trends.

DAX
TrendsParameter =
DATATABLE(
"ViewSelectionCategory", STRING,
"Order", INTEGER,
{
{"Yearly", 1},
{"Quarterly", 2},
{"Monthly", 3}
}
)

Create a measure to capture the selected parameter from the slicer.

SelectedTrend =
SELECTEDVALUE(TrendsParameter[ViewSelectionCategory])

Create a single measure that dynamically calculates the value based on the selected parameter.

DynamicTrendMeasure =
SWITCH(
[SelectedTrend],
"Yearly",
CALCULATE(
SUM('YourDataTable'[YourValueColumn]),
DATESYTD('YourDateTable'[Date])
),
"Quarterly",
CALCULATE(
SUM('YourDataTable'[YourValueColumn]),
DATESQTD('YourDateTable'[Date])
),
"Monthly",
CALCULATE(
SUM('YourDataTable'[YourValueColumn]),
DATESMTD('YourDateTable'[Date])
),
BLANK()
)

Use the DynamicTrendMeasure in your matrix visual and bar chart. The measure will dynamically update based on the selection made in the slicer.

Add a slicer to your report using the TrendsParameter table and the ViewSelectionCategory column. This will allow users to switch between Yearly, Quarterly, and Monthly trends.