cancel
Showing results for
Did you mean:

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

Regular Visitor

## NEED HELP: Calculated Measure Filter

New to Power BI so just learning but I'm trying to create a calculated measure to rank and return top 10 dependent on condition in another column.

Data example:

 Country Result Tunisia 1 Angola 0 Morocco 1 Spain 1 Germany 0 Denmark 0 Tunisia 1 Denmark 1 Morocco 0

I want to filter out all '0' cols, then return each country aggregate, then ranked. e.g.

Tunisia - 40

Spain   - 10

etc.

I amn't grasping the flow in DAX at all yet.

I've tried a few things including SUM(RANK(Table[col], ...not sure how to finish that

Top 10 =

VAR Max = MAXX(FILTER(ALL('table'), 'table'[Country] = SELECTEDVALUE('table'[Results])),'table'[Results])...

I'm at a complete loss and would appreciate an explanation of how to approach the breakdown of this in DAX

This seems close but I need to breakdown further, it just returns the number of distinct countries, but I don't know how to apply row iterator like COUNTX, COUNTROWS etc to get a sum over distinct countries

TOP 10 =
CALCULATE(
DISTINCTCOUNT('table'[Country]),
'table'[Result] = 1
)
1 ACCEPTED SOLUTION
Community Support

Hi @MandoMongo ,

The Table data is shown below:

1. Use the following DAX expression to create a table

``Table 2 = SUMMARIZE(FILTER('Table','Table'[Result] <> 0),'Table'[Country],"Count",COUNT('Table'[Result]))``

2.Use the following DAX expression to create a column in 'Table2'

``Rank = RANKX('Table 2','Table 2'[Count],,DESC,Dense) ``

3.Final output

SUMMARIZE function (DAX) - DAX | Microsoft Learn

RANKX function (DAX) - DAX | Microsoft Learn

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

Hi @MandoMongo ,

The Table data is shown below:

1. Use the following DAX expression to create a table

``Table 2 = SUMMARIZE(FILTER('Table','Table'[Result] <> 0),'Table'[Country],"Count",COUNT('Table'[Result]))``

2.Use the following DAX expression to create a column in 'Table2'

``Rank = RANKX('Table 2','Table 2'[Count],,DESC,Dense) ``

3.Final output

SUMMARIZE function (DAX) - DAX | Microsoft Learn

RANKX function (DAX) - DAX | Microsoft Learn

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors