Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
New user, love the product! I have quite a complicated question (for a beginner) but I am sure it will be easy enough for one of you to solve.
I am analysing medical insurance claims data submitted by practices all over South Africa. I have 1 Fact table and 3 Dimension tables: Claims Fact, Date Dimension, Patient Dimension and Practice Dimension. The Data Model is depicted below:
Don’t worry about the other tables out of the picture for now. I want 2 slicers. The first should list all the Practice Nos (from the Practice Dimension), The second should have 2 benchmarks - 'Province' and 'National'. There will be two charts, most likely Dot Plots - haven't decided yet, but the format doesn’t really matter for now.
The first chart will have the month (from the Date Dimension) running along its axis and display the average number of patients seen for that particular month for the selected Practice No. The second chart will again have the month (from the Date Dimension) running along its axis and display the average claim amount (from the Claim Fact table) per patient for that particular month for the selected Practice No.
Easy enough so far but now it gets a little complicated. I want to give the user the option to benchmark their selected Practice No against all other practices operating in the same province as well as nationally. To do this, the user will select their chosen benchmark option from the second slicer mentioned above. However, whichever benchmark is chosen, that Practice No's values should not be included in the chosen benchmark.
For example, taking the ave number of patients seen case. If the user selects to benchmark the Practice No by province, the ave number of patients seen in the same province as the selected practice should be displayed excluding the selected Practice No's values. Same if the user wants to benchmark the Practice No nationally. The national benchmark should be calculated excluding the selected Practice No's values.
The situation gets slightly more complex if the user selects to benchmark both by province and nationally. In this case the provincial benchmark should be calculated without the selected Practice No's values and the national benchmark should be calculated without the province and the selected Practice Nos values.
In Summary:
Practice Province Province Benchmark
Practice A Western Cape Ave no of patients for all practices in the Western Cape excluding Practice A
National Benchmark
Ave no of patients for all practices in South Africa excluding Practice A
Province and National Benchmark
Ave no of patients for all practices in the Western Cape and then nationally excluding Practice A and the Western Cape
And then same logic needs to be applied to the other chart which displays the ave claim amount per patient. Shooo bit of a head twister, I know. Any help would be greatly appreciated.
Kind Regards,
Paul
Solved! Go to Solution.
@Anonymous paul this is quite a good video to watch if you want to use a slicer to move between meaures, some "lekker" tips in here too
this starts at minute 11 ish
Proud to be a Super User!
@Anonymous paul this is quite a good video to watch if you want to use a slicer to move between meaures, some "lekker" tips in here too
this starts at minute 11 ish
Proud to be a Super User!
You should be able to detect the precense of a filter on Practice and/or Province, using for example HASONEVALUE(), and define your benchmark formulas to exclude the values for those dimensions when calculating your averages. You may need to your share your benchmark measures to get help with that.
Thanks for the input. I will have a look and revert.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |