Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |