Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
yasbos
Resolver I
Resolver I

Identifying the three measures with top values

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.

 

MonthP1P2P3P4P5P6P7P8P9P10
AprNNYYNYYYYY
AugYYYYYNYNNY
DecNNYYNYYNYN
FebYNNYNNYYNN
JanYNYYYYNYYY
JanYYYYNNNYYY
JulNNYYYNNYNY
JulNNNYNYYYYY
JulYYYYNNNNNY
MarNNYNNNNYYY
MarNNNYNNNNNY
MayYYYYYYNNNN
NovNNNNNYNYYY
OctYYYYNYYYYY
SepNNYYYNYYYN
1 ACCEPTED SOLUTION
AnalyticsWizard
Solution Supplier
Solution Supplier

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

View solution in original post

1 REPLY 1
AnalyticsWizard
Solution Supplier
Solution Supplier

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.