5 up 5 down for Tax Rate-Syncing a slicer with a disconnected table

[tjohnson]

Good afternoon.

I have previously posted a help request on 5 up 5 down for Population. A link to this topic is [5 Up 5 down for Population](https://forum.enterprisedna.co/t/selecting-5-above-and-5-below-dynamically/15967/8?u=tjohnson )

I received assistance on this from @ankit, and now have a working solution–which is in the 1st page of my power bi file–attached.

Since that time, I have had a request for a 5 up 5 down comparison on Total Taxes received in a fiscal or calendar year–this is not working correctly.

I have googled online and in the forum, and I have not found similar items such as this.
Basically–what I am wanting to do is to select a city from a drop down, and have displayed in the table the selected City’s data, and also the data for those cities that are 5 up and 5 down for their Total Taxes Rank. I have a measure for Taxes Rank that is working–on page Pop Rank and Taxes Rank-not working-- if it is not trying to do a comparison. The problem is when I am trying to compare based on 5 up 5 down on tax rank–it only shows the selected city.

There are several measures that are on the table for 5 up 5 down-taxes rank-not working–I am putting them in a folder 5 up 5 down Tax Rank. Some are working, some are not.

Essentially–on the Population table (the first page) is controlled by the measures Rank Display. On the Table–I put the Rank Display = 1 on the table–this allows the city selected in the Municipality drop down to control what is put in the table–in the measure–it calculates those cities that are 5 above and 5 below the population rank of the selected city–and brings them into the table. I put the Rank Display on the table grid–so you can see that the selected city and those 5 above and 5 below are in the table.

I tried to do similar on the Taxes Rank page–but this is not working. Taxes Rank Display = 1 is only allowing the selected city to come into the table–I believe this is due to the fact that the slicer for Municipality is also the same filed that is on the table–from the tbl1City table.

I then tried the 3rd page–5 up 5 down-taxes rank working-disconnected table
–this is currently working–however, now I have another problem.
I am wanting to sync the slicers for all pages so that the Municipality selected is from the tbl1city-Municipality field. This works on the 1st 2 pages–however on this page–since the city is selected from a disconnected slicer–it does not work.

Can I filter the disconnected slicer to choose which ever field is selected on the tbl1city Municipality field?

If not–can I sync the tbl1city Municipality slicer with the Disconnected City Slicer?

Or–if you have an alternative solution, I would be open to that as well.

I have been working on this for several days now. Any assistance would be appreciated. I am fairly new to Dax and am not a programmer. Thank you so much for your time.

5 above 5 below tax rank.pbix (9.2 MB)

[JBocher]

Hi @tjohnson,

Here is my solution to your problem.
5 above 5 below tax rank.pbix (9.2 MB)

I’ve created a JBocher Measures table to put inside all the measures I’ve created.
The 3 new pages are the “Duplicate” ones.

To manage to filter on the +5 and -5 ranking of the selected city, you need to work with the disconnected cities for all three pages.

Then you need to change a little your measures to take in account the selected city each time.

For instance, here is the measure you need to display the ranking of the city you selected on all the lines of your table.

I’ve also changed the measures to always work with the column Municipality of the table tbl1City.
I’ve deleted the Many to Many relationship on your model, because you need to always work with One to Many relationship if it’s possible.

Finally, I saw that the measures in the table of your first page don’t show values of the filtered cities.

For instance, for the average, it was the average of all the cities.
To correct this anomaly, you only need to change “ALL” with “ALLSELECTED” in all your measures.
For instance :

I hope it will help you.

Best regards,
JBocher

[tjohnson]

Thank you for your reply, @JBocher

I may not have articulated exactly what I was wanting.

On the 1st page–5 up 5 down-pop rank-working–this solution is working–I know it has the many to many relationship, but I am not quite understanding how to get around it, but for the moment it does work.

The display is as follows.

On the slicer I used Tbl1City-Municipality, and on the table–I used the tblCityPopulationByYear and the Municipality field.

The slicer for Tbl1City with the Municipality field is on all pages of my report–they are all synced to each other (this is just a demo, my file has MANY more pages). This will provide the estimated population for each City for the Fiscal Year and their individual tax revenue for that time frame.

What I am wanting is to do the same idea for the Tax Revenue Field–I can get the tax rank for each city’s tax revenue, but when I try to put it in this same visualization, it doesn’t work correctly. If you can help me fix that one field, I think it will solve my problem.

Since I was not able to get the Tax Revenue Rank to work on this same type of visualization, I tried Page 2–which works for the selected City.

However–I cannot get the cities that are 5 above and 5 below the selected city in rank of their tax revenue to show up–On the slicer I used Tbl1City-Municipality, and on the table–I used the same filed for Municipality–which I believe is my problem–but I don’t know how to get around it.

So I tried the 3rd page–which I used a disconnected slicer for the chosen City–

This shows what I am trying to achieve, however–Now I have another problem–I can’t sync the slicer to the tbl1City Municipality slicer that I want to control ALL the pages.

On yours, it is showing all cities amounts–not the individual amounts for each city.

I hope that helps to clarify.

So, essentially–
I am need to know how to

make table 1 work for the Tax Rank field
or
make Table 2 to work to show the 5 up or 5 down
or
make Table 3 to sync the disconnected table City slicer to the tbl1City-Municipality slicer

Any ones of these could help me achieve what I am trying to do.

Thank you so much for your time and response. I hope you can help me achieve my goals.

[JBocher]

You have difficulties to find a solution to your problem, because you never uses the same Municipality column for tables or measures.

One advice when you want to select a dimension (here a city) to compare some measures, it’s better to create a disconnected table. This will ensure you not to have some filters you don’t want.
That’s why you have created new measures with “ALL( tbl1City)” to remove the filter on the tbl1City table.
For the Many to Many relationships, it could lead to strange results sometimes and really bad performances with large databases.

I have an idea for your Taxes Rank measure if you want to keep the same filter on tbl1City
You need to rank it with tblCityPopulationByYear and to remove all the filters on tbl1City table.
Try something like that to apply on your table 1 (it’s replacing the Taxes Rank measure) :

Then you can duplicate with Taxes Rank Test what you have done for RANK DISPLAY and RANK DISPLAY 2. But the ranking of the selected city is not the same for population and tax so you can’t do both filter in the same time.

I’ve added some measure to your table 1.
5 above 5 below tax rank (1).pbix (9.2 MB)

I hope this time it will answer to your problem

Best regards,
JBocher

[tjohnson]

@JBocher
Thank you SOOOO much for your assistance!! It is working now.

As far as using several different Municipality columns on tables and measures–normally I use the tbl1City Municipality column. It was the 5 up 5 down that caused me to have to do this differently. I understand what you are saying.

I will implement this in my file. I REALLY do appreciate your time. I am self taught (with the help of this forum and Enterprise DNA and Google) and am not a programmer-so sometimes, I just have to go with what “works”–not necessarily what is best.

Again, thank you so much for your help.