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,
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!
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
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!
@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
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.
Does this seem to make a little more sense? Thank you very much for your time!
Regards,
shali
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
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.
What happens if two countries happen to have the same value on which you rank? Then your logic will still be valid?
Best
Darek
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 |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
69 | |
48 | |
44 | |
18 | |
15 |