Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all, I'm trying to create a DAX measure that will show onther conditions a population has when a slicer with chronic conditions is selected.
Scenario: I want to see what are the top N other diseases patients have when I select Heart Disease.
My table has a column with Chronic Conditions which is what the Slicer is referencing.
When I select a slicer value, get the top N conditions and their associated Medical spend amount.
Any help greatly appreciated.
Jason
Solved! Go to Solution.
Hi Jason,
Maybe you can try this solution. You can check it out in this file.
1. Create a table of all the conditions.
Conditions = VALUES ( Table1[Condition] )
2. Establish a relationship between 'Conditions' and 'Table1'.
3. Create a measure for all the related costs.
Co-Cost = VAR allConditions = VALUES ( Conditions[Condition] ) VAR selectedPatients = CALCULATETABLE ( VALUES ( 'Table1'[Patient] ), FILTER ( ALL ( Table1 ), 'Table1'[Condition] IN allConditions ) ) RETURN CALCULATE ( IF ( MIN ( 'Table1'[Patient] ) IN selectedPatients, SUM ( Table1[Paid] ), BLANK () ), ALL ( Conditions[Condition] ) )
4. Create a measure for the rank of the cost of one patient.
ConditionRankForOnePatient = RANKX ( ALL ( 'Table1'[Condition] ), [Co-Cost],,, DENSE )
5. Create a table visual and a slicer.
Best Regards,
Dale
Hi @jasonbakersd,
1. How to calculate the top N? The amount of Paid?
2. Why do you want to get top N after a selection? Do you mean the relative top N? For example, if Heart Disease is rank 5, you want those conditions that are rank 6, 7, 8. Right?
3. What is Co-Morbidity? It doesn't have any special features.
Best Regards!
Dale
Hi v-jiascu,
With chronic medical conditions, a patient can have related conditions.
For instance, a patient with Heart Disease may have COPD, High Blood Pressuer, Morbid Obesity. These are the Co-Morbidity conditions that we want to analyze.
In our medical claims table each discrete medical claim has a condition associated to it.
So the use case is,
Select a Chronic Condition from a slicer.
From this selection, get the universe of patients who have the condition.
Using this population, calculate the medical paid amounts for any additional conditions he/she has.
Display in results in a table or visual ( where we can filter on the top 10 by medical paid amount).
Thanks,
Jason
Hi Jason,
Maybe you can try this solution. You can check it out in this file.
1. Create a table of all the conditions.
Conditions = VALUES ( Table1[Condition] )
2. Establish a relationship between 'Conditions' and 'Table1'.
3. Create a measure for all the related costs.
Co-Cost = VAR allConditions = VALUES ( Conditions[Condition] ) VAR selectedPatients = CALCULATETABLE ( VALUES ( 'Table1'[Patient] ), FILTER ( ALL ( Table1 ), 'Table1'[Condition] IN allConditions ) ) RETURN CALCULATE ( IF ( MIN ( 'Table1'[Patient] ) IN selectedPatients, SUM ( Table1[Paid] ), BLANK () ), ALL ( Conditions[Condition] ) )
4. Create a measure for the rank of the cost of one patient.
ConditionRankForOnePatient = RANKX ( ALL ( 'Table1'[Condition] ), [Co-Cost],,, DENSE )
5. Create a table visual and a slicer.
Best Regards,
Dale
Dale,
Thanks for the reply, I'll take a look at this later and update the response.
-Jason
Hi @jasonbakersd,
Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.
Best Regards!
Dale
Hi,
Your question is not clear. Show a sample dataset and the expected result.
Ashish,
I've added a picture with data and how I'd like to see the results
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |