Ranking and Count using DAX

Question

Hi,&nbsp;&nbsp;I'm pretty new to DAX and have been trying to implement the following logic in a visual. The aim to have a rolling window of 7 countries , based on the ranking of a specific country that has been selected.&nbsp;&nbsp;I've included the logic that I've though of and how it would look like when the selected country changes. I'm tried playing around with DISTINCTCOUNT and SELECTEDVALUE but i'm not having much luck.&nbsp;&nbsp;&nbsp;If anyone here can point me in the right direction, that would be awesome.&nbsp;&nbsp;For ease of reference:&nbsp;Count refers to the count of all distinct countries.&nbsp;Ranking refers to&nbsp; the ranking of the chosen country.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1st Rule:&nbsp; If the Count &lt;= &nbsp;7, then include all the countries.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2nd Rule: If the Count &gt; 7 and Ranking &lt;= 4, include the top 7 countries&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3rd Rule: If the Count &gt; 7 and Ranking &gt; (Count – 3), include the bottom 7 countries&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4th Rule: Else, take (+/- 3 ) of the Ranking. &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Chosen country&nbsp;&nbsp;CountryAirWeightRankingItalyGreeceNorwayGermany98712nd Rule&nbsp;&nbsp;Italy98222nd Rule&nbsp;&nbsp;France72332nd Rule&nbsp;&nbsp;United Kingdom69642nd Rule&nbsp;&nbsp;Netherlands65652nd Rule&nbsp;4th RuleSpain62162nd Rule&nbsp;4th RuleBelgium + Luxembourg61172nd Rule&nbsp;4th RuleNorway5438&nbsp;&nbsp;4th RuleSwitzerland5219&nbsp;&nbsp;4th RuleSweden50110&nbsp;&nbsp;4th RuleAustria38911&nbsp;&nbsp;4th RuleDenmark35612&nbsp;3rd Rule&nbsp;Irish Republic25413&nbsp;3rd Rule&nbsp;Finland27814&nbsp;3rd Rule&nbsp;Portugal16715&nbsp;3rd Rule&nbsp;Greece14516&nbsp;3rd Rule&nbsp;Iceland13417&nbsp;3rd Rule&nbsp;Other Western Europe1218&nbsp;3rd Rule&nbsp;Guernsey&nbsp;19excluded as it has NULL AirWeight&nbsp;Thanks!

anonymous · Answer

Yeah... it's all good and can be done but... what is it that you want to calculate? Is it, for instance, for each country the average of AirWeight over the countries that you want to pick up through the rules?

Best

Darek

vaishali12 · Answer

Hi&nbsp;Anonymous&nbsp;,&nbsp;&nbsp;I would want to create a column 'To include' where it will be 1 if it fits the rules, or 0 if it doesn't.&nbsp;This column would be used to filer out the countries that are to be shown on a visual (a bar chart for example) which ranks the countries with 'to Include' = 1 based on the Air Weight.&nbsp;&nbsp;Thanks!

anonymous · Answer

@vaishali12&nbsp;wrote:Hi&nbsp;@darlove&nbsp;,&nbsp;&nbsp;I would want to create a column 'To include' where it will be 1 if it fits the rules, or 0 if it doesn't.&nbsp;Where do you want to create the column? I still don't understand... Would you please create some picture of what it really is you want and paste in here? You can draw on a piece of paper (but legibly!), take a pic with your phone and post here... That'll be the fastest way.&nbsp;BestDarek

vaishali12 · Answer

Hi&nbsp;Anonymous&nbsp;,&nbsp;&nbsp;I apologise for the delay in getting back to you.&nbsp; I've created a few snapshots of the original table with the snapshots and how the 'To Include' column in the original table changes (with the rules mentioned in the first post as the underlying logic) as the slicer selection changes.&nbsp;&nbsp;1. Original table with no slicer selection2. 'To Include' column changes with new slicer selection3. 'To Include' column changes with new slicer selectionDoes this seem to make a little more sense?&nbsp; Thank you very much for your time!Regards,shali

anonymous · Answer

This is still not clear...&nbsp;Where do you get Ranking from? Is this a field in one of your tables? Or is it a measure that adjusts accordingly to the currently visible countries in the column? I'm asking because if it's a column in a table, then it does not look like the rules you've given are going to work. If Ranking is a measure, then...&nbsp;Best would be if you could take a screenshot of your model and paste in here. This would help a lot.&nbsp;Thanks.&nbsp;BestDarek

Forum Discussion

Ranking and Count using DAX

7 Replies

Related content

Duplicate ranking

Ranking KPI card

Counting Ranks

Counting nulls

Dynamic Ranking

			Chosen country
Country	AirWeight	Ranking	Italy	Greece	Norway
Germany	987	1	2nd Rule
Italy	982	2	2nd Rule
France	723	3	2nd Rule
United Kingdom	696	4	2nd Rule
Netherlands	656	5	2nd Rule		4th Rule
Spain	621	6	2nd Rule		4th Rule
Belgium + Luxembourg	611	7	2nd Rule		4th Rule
Norway	543	8			4th Rule
Switzerland	521	9			4th Rule
Sweden	501	10			4th Rule
Austria	389	11			4th Rule
Denmark	356	12		3rd Rule
Irish Republic	254	13		3rd Rule
Finland	278	14		3rd Rule
Portugal	167	15		3rd Rule
Greece	145	16		3rd Rule
Iceland	134	17		3rd Rule
Other Western Europe	12	18		3rd Rule
Guernsey		19	excluded as it has NULL AirWeight