Dax Ranking based on 3 categories

[rajivraina]

Hi all,

I am having trouble creating a formula to rank values based on multiple categories. The scenario is as follows: 

I have macroeconomic data on countries, a sample of which is shown below. The "Indicator Rankx 2" column is incorrect and what I need help with. 

The Grouping Measure groups countries into 3 different buckets (high/middle/low), and each country has a few different indicators (the one shown is "Balance of Trade"). I would like the Rank measure to rank countries within their grouping, year, and indicator. So in the picture above, it should have only three copies of any given rank, one for each grouping (since year and indicator are constant in this case). I also need the rank measure to NOT take into account any blanks in "Value 4.55". The formula I have so far is below, but clearly it is not working very well... 

Indicator Rankx 2 = IF(FIRSTNONBLANK(Indicator[Indicator],1)="Balance of Trade"&&NOT(ISBLANK([Value 4.55])),RANKX(FILTER(ALL(GNI),[Value 4.55]<>0&&[Value 4.55]=CALCULATE([Value 4.55],VALUES('Date'[Date]))<>BLANK()&&[Grouping Measure]=CALCULATE([Grouping Measure])),[Value 4.55],,DESC,Skip))

Thank you in advance for any help,

Raj

[TeigeGao]

Hi @rajivraina ,

Please refer to the following DAX query:

Indicator Rankx 2 =
IF (
    MIN ( Table1[Value 4.55] ) = BLANK (),
    BLANK (),
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            ALL ( Table1 ),
            Table1[Year] = MIN ( Table1[Year] )
                && Table1[Indicator] = MIN ( Table1[Indicator] )
                && Table1[Grouping Measure] = MIN ( Table1[Grouping Measure] )
                && Table1[Value 4.55] > MIN ( Table1[Value 4.55] )
        )
    ) + 1
)

The result will like below:

Best Regards,

Teige

[rajivraina]

Hi @TeigeGao ,

When I try using that formula I get an error "Column "Value 4.55" in table 'TE Data' cannot be found or may not be used in this expression. The value is definitely in my fields though so it must be that it cannot be used in the expression..but why?

Both "value 4.55" and "grouping measure" are measures, not raw data or calculated columns, does that impact this formula at all? 

Also "Value 4.55", "Category", and "Grouping Measure" are all in seperate tables (relationships exist), maybe that is also a problem?

Thanks,

Raj