Forum Discussion

MandoMongo's avatar
MandoMongo
Regular Visitor
2 years ago
Solved

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:

CountryResult
Tunisia1
Angola0
Morocco1
Spain1
Germany0
Denmark0
Tunisia1
Denmark1
Morocco0

 

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
)
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi MandoMongo ,

    The Table data is shown below:

    Please follow these steps:
    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi MandoMongo ,

    The Table data is shown below:

    Please follow these steps:
    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.