Identifying the three measures with top values

[yasbos]

Hi. I have 18 different measures. Each calculates the percentage of Y value for its corresponding parameter. So, basically, I have a table (example shown below) where I have 18 parameters. The below example shows just 10 for brevity. I need to report on the percentages of occurance of Y value each month for each of the parameters (e.g. in the below table, P4 and P10 are a tie at the top for July, and P8 is in second place.) If there is a tie, then I can pick either one. However, it's going to be unlikely in the actual data, since there is going to be a much larger data set with a very low chance that multiples will be exactly the same at a two percentage point precision. I am most interested in the top, say, 3 each month. I don't have the luxury of creating any tables or calculated tables because I consume what is available to me through Analysis Services tabular model. My goal is to be able to identify the top 3 parameters (having the highest percentages of Ys) within the selected slicer date range. Please, remember that I can't create physical or calculated tables. If that is the only option, then please still share. I will then see if I could get IT to create it. I would've used an if-statement (or perhaps a switch) if it were only a couple of parameters, but with 18 parameters, it's probably a bad idea. Thanks so much.

Month	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10
Apr	N	N	Y	Y	N	Y	Y	Y	Y	Y
Aug	Y	Y	Y	Y	Y	N	Y	N	N	Y
Dec	N	N	Y	Y	N	Y	Y	N	Y	N
Feb	Y	N	N	Y	N	N	Y	Y	N	N
Jan	Y	N	Y	Y	Y	Y	N	Y	Y	Y
Jan	Y	Y	Y	Y	N	N	N	Y	Y	Y
Jul	N	N	Y	Y	Y	N	N	Y	N	Y
Jul	N	N	N	Y	N	Y	Y	Y	Y	Y
Jul	Y	Y	Y	Y	N	N	N	N	N	Y
Mar	N	N	Y	N	N	N	N	Y	Y	Y
Mar	N	N	N	Y	N	N	N	N	N	Y
May	Y	Y	Y	Y	Y	Y	N	N	N	N
Nov	N	N	N	N	N	Y	N	Y	Y	Y
Oct	Y	Y	Y	Y	N	Y	Y	Y	Y	Y
Sep	N	N	Y	Y	Y	N	Y	Y	Y	N

[AnalyticsWizard · Accepted solution]

@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 👍