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.
Hello,
I need to rank dynamically the dataset below by Area Leased column.
Tenant Name | Quarter | Year | Address | State | Suburb | Area Leased |
Jon K | Q1 | 2021 | 250 Victoria Street | NSW | Sydney | 70,178 |
Michel F | Q1 | 2021 | 285 Palmers Road | VIC | Melbourne | 70,000 |
Huan Jyan | Q1 | 2021 | 917 Boundary Road | VIC | Melbourne | 70,000 |
Justin T | Q1 | 2021 | Lot 4, Skyline Crescent | NSW | Sydney | 36,171 |
Chin Y | Q1 | 2021 | 2 Maker Place | VIC | Melbourne | 30,000 |
Wayne T | Q1 | 2021 | 500 Dohertys Road | VIC | Melbourne | 25,000 |
Bryan Y | Q1 | 2021 | 11-13 Percy Street | NSW | Sydney | 20,480 |
Ahram J | Q1 | 2021 | 11-167 Palm Springs Road | VIC | Melbourne | 19,960 |
The outcome I need:
For example, if the user selects "3" in the slicer, the table above must show only the first 3 tenants' names with the higher Leased Area.
Thanks
Solved! Go to Solution.
Hi, @Anonymous
Thank you for your feedback.
Because the condition has been changed, I amended the measures.
Showing the amount by ranking of amount + alphabetical order + not showing blank name.
I did not consider the ranking by each area name. If you want to, you can simply add in the condition for area or city name.
Also, if you want to have a slicer, you can simply replace the ALL function to ALLSELECT in order to dynamically be changed by the date-slicer. I already changed to allselect.
Please check the link down below, and all measures are in the sample pbix file.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
https://www.dropbox.com/s/36yyza7yooeqbdn/fabnishiv2.pbix?dl=0
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @Anonymous
Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @Jihwan_Kim thanks for getting back to me. The solution almost worked. In my real data set I actually have the same Numbers and null please see below the first and last row that I added
Essentially the tenant FK and Ahram J have the same area leased of 19,960 however the rank cannot be the same for them, the Ahram must be first because of the alphabetical order.
We also need to ignore the tenant name with empty name and value on the area leased.
Tenant Name Quarter Year Address State Suburb Area Leased
Q1 2021 Lot 4, Skyline Crescent NSW 100,000
Jon K Q1 2021 250 Victoria Street NSW Sydney 70,178
Michel F Q1 2021 285 Palmers Road VIC Melbourne 70,000
Huan Jyan Q1 2021 917 Boundary Road VIC Melbourne 70,000
Justin T Q1 2021 Lot 4, Skyline Crescent NSW Sydney 36,171
Chin Y Q1 2021 2 Maker Place VIC Melbourne 30,000
Wayne T Q1 2021 500 Dohertys Road VIC Melbourne 25,000
Bryan Y Q1 2021 11-13 Percy Street NSW Sydney 20,480
Ahram J Q1 2021 11-167 Palm Springs Road VIC Melbourne 19,960
F K Q1 2021 500 Dohertys Road VIC Melbourne 19,960
I also have the date linked to my dim_date which I need to reset the rank when changing the dates, is that possible?
Hi, @Anonymous
Thank you for your feedback.
Because the condition has been changed, I amended the measures.
Showing the amount by ranking of amount + alphabetical order + not showing blank name.
I did not consider the ranking by each area name. If you want to, you can simply add in the condition for area or city name.
Also, if you want to have a slicer, you can simply replace the ALL function to ALLSELECT in order to dynamically be changed by the date-slicer. I already changed to allselect.
Please check the link down below, and all measures are in the sample pbix file.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
https://www.dropbox.com/s/36yyza7yooeqbdn/fabnishiv2.pbix?dl=0
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Anonymous , You can create a measure rank
Rankx(allselected(Table[Tenant Name]), calculate(Sum(Table[Area Leased])),,desc,dense)
or
Rankx(allselected(Table), calculate(Sum(Table[Area Leased])),,desc,dense)
You can filter for 3 in visual level
of you can use TOP N with what if
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |