Identifying the three measures with top values

yasbos 

Given your requirements and constraints within the Analysis Services Tabular model, the best approach to identify the top three parameters with the highest percentage of 'Y' values each month—without creating new tables—is to leverage DAX measures. You can create measures to calculate the percentage of 'Y' values for each parameter and then use these measures to rank the parameters.

Here’s a conceptual breakdown on how to proceed:

1. Create Percentage Measures: First, define a DAX measure for each parameter to calculate the percentage of 'Y' values. For instance, for parameter P1, the measure might look like:

P1 Percentage =
CALCULATE(
DIVIDE(
COUNTROWS(FILTER('Table', 'Table'[P1] = "Y")),
COUNTROWS('Table')
)
)

You would replicate this for P2, P3, ..., P18.

2. Ranking Measures: Next, create a measure to rank these parameters by their percentage value. The ranking measure can look something like this for each month:

Rank P1 =
RANKX(
ALL('Table'[Month]),
[P1 Percentage],
,
DESC,
Dense
)

Repeat this measure for all parameters (P1 through P18).

3. Top 3 Parameters Measure: Create a measure that dynamically identifies which parameters are in the top 3 for the selected month. This can be somewhat complex because DAX doesn’t support dynamic pivot or similar operations directly. You might need to create a measure that checks the rank and constructs a string or a concatenated label of parameters ranking in the top 3.

Top 3 Parameters =
VAR Top1 = TOPN(1, FILTER(ALL('ParameterTable'), [Rank Measure] = 1), [Percentage Measure], DESC)
VAR Top2 = TOPN(1, FILTER(ALL('ParameterTable'), [Rank Measure] = 2), [Percentage Measure], DESC)
VAR Top3 = TOPN(1, FILTER(ALL('ParameterTable'), [Rank Measure] = 3), [Percentage Measure], DESC)
RETURN
CONCATENATEX(
VALUES({Top1, Top2, Top3}),
[Parameter],
", "
)

This example is illustrative and may require adjustments based on your exact data structure and the capabilities of your environment.

4. Visualization: Use these measures in your reports to display the top 3 parameters. You can visualize these using standard Power BI visuals like tables, bar charts, or conditional formatting to highlight these parameters dynamically based on slicer selections.

If creating these measures is not feasible within your current permissions, you might need to request that IT creates a calculated table in the Analysis Services model, which simplifies this process by allowing more complex transformations and pre-aggregated calculations.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍