RANKXX Issues

[CAPEconsulting]

I have multiple data tables in a relationship like 

Then on my dashboard I have the following set up

 

The chart at the bottom is juts the values.

And the column chart in the middle is a rank based chart that uses 2 meaures: -

1. RankingH SA3 = SUM('SA3-MH Care'[Value])

2. Rank SA3 = RANKX(ALLSELECTED('SA3-MH Care'), [RankingH SA3],,DESC,Skip)

All works well.

However when I make a selection of a region, the bottom chart filters through to the correct small areas and shows the right data.

But the ranking chart does not filter down to the right areas and apart from the right areas all areas have the same rannking

 On scrolling to the right you see that the right areas are being ranked but why are the other areas not getting filtered out ?? and why are they being ranked?

Any advise guys??

Also any ideas for doing clever visualisation of rankings???

Abhijeet

[TomMartens]

Hey,

I'm wondering why you don't wrap both measures in the CALCULATE( ... function), this could explain why something isn't filtered as you expect.

Hope this already helps

[CAPEconsulting]

@TomMartens

Thanks for the reply, but that did not make any difference....

Abhijeet

[Anonymous]

I'm having a slightly hard time reading the relationship diagram, but ... it looks like sa3byphn isn't sa3-mh care... and therefore slicing on phname isn't impacting?

[CAPEconsulting]

Hi @Anonymous

Does the attached make it clear

[v-yuezhe-msft]

@CAPEconsulting,

Is the  RankingH SA3 measure filtered correctly by PHNName? Would you mind sharing the sample data of your tables or PBIX file for us to analyze?

Regards,
Lydia

[CAPEconsulting]

@v-yuezhe-msft Cant share the file publocally. Is ther a way to PM it to you.

[CAPEconsulting]

@v-yuezhe-msft @Anonymous @TomMartens

I have changed my DAX measure as 

Rank SA3 = IF(HASONEVALUE('SA3-MH Care'[Value]), RANKX(ALLSELECTED('SA3-MH Care'[Name]), CALCULATE([RankingH SA3], ALL(PHN[PHNName])),,DESC,Skip))

Now the filtering works all perfectly but I want the ranks to be ranks acros all smalelr areas not just across the areas that are filtered. WHat am I missing?

[TomMartens]

Hey,

I've to admit that I'm not fully understand your model, for this reason I can just some general considerations.

Each calculation (measure) uses the underlying filtered rows (the table is filtered by selections in all slicers and also by row and column headers), this concept or "universal truth" is called Filter Context. 

Using CALCULATE(<expression>) applies the current selections (slicers, columns, headers) to the table and calculates the expression.

Sometimes this is exactly what you need, e.g. caluculating the sum of a numeric column in a table.

But sometimes you have to expand the table or furthe reduce (this is how I think about dax statements: do I have to expand or reduce the rows of the table before my calculation can be applied).

If you say "not just the areas that are filtered" means you have to expand the rows, this can be achieved by using ALL.

Maybe you have to try something like this

RANKX(
CALCULATETABLE('your table',
  ALL('your table'[column one you want to ignore]),
  ALL('your table'[column two you want to ignore]),
  ALL(...)
),
...
)

Hope this helps