cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rajivraina Regular Visitor
Regular Visitor

Dax Ranking based on 3 categories

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. 

 

 

Capture.PNG

 

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

2 REPLIES 2
TeigeGao Super Contributor
Super Contributor

Re: Dax Ranking based on 3 categories

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:

Snipaste_2019-04-10_16-35-45.png

Best Regards,

Teige

rajivraina Regular Visitor
Regular Visitor

Re: Dax Ranking based on 3 categories

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