Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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
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?
Hi @Anonymous
Does the attached make it clear
@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
@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?
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
User | Count |
---|---|
85 | |
85 | |
67 | |
61 | |
56 |
User | Count |
---|---|
137 | |
110 | |
92 | |
84 | |
69 |