Ranking and Count using DAX

[vaishali12]

Hi, 

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. 

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.  

If anyone here can point me in the right direction, that would be awesome. 

For ease of reference:

 Count refers to the count of all distinct countries.

 Ranking refers to  the ranking of the chosen country.

                1st Rule:  If the Count <=  7, then include all the countries.

                2nd Rule: If the Count > 7 and Ranking <= 4, include the top 7 countries

                3rd Rule: If the Count > 7 and Ranking > (Count – 3), include the bottom 7 countries

                4th Rule: Else, take (+/- 3 ) of the 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

Thanks!

[Anonymous]

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]

Hi @Anonymous , 

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. 

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. 

Thanks!

[Anonymous]

@vaishali12 wrote:

Hi @darlove , 

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. 

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.

Best

Darek

[vaishali12]

Hi @Anonymous , 

I apologise for the delay in getting back to you.  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. 

1. Original table with no slicer selection

2. 'To Include' column changes with new slicer selection3. 'To Include' column changes with new slicer selection

Does this seem to make a little more sense?  Thank you very much for your time!

Regards,

shali

[Anonymous]

This is still not clear...

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...

Best would be if you could take a screenshot of your model and paste in here. This would help a lot.

Thanks.

Best

Darek

[vaishali12]

The ranking is a calculated mesaure that I've managed to figure out using DAX, so it's not built into the original table. Only the countries and Airweight are part of the original dataset. 

[Anonymous]

What happens if two countries happen to have the same value on which you rank? Then your logic will still be valid?

Best

Darek