cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jasonbakersd
Helper I
Helper I

DAX - How to get other values based on a slicer value. Co-Morbidity

 

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.  

 

comorb.png

 

 

 

 

Any help greatly appreciated.

 

Jason

1 ACCEPTED 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.DAX - How to get other values based on a slicer value.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-jiascu-msft
Microsoft
Microsoft

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.DAX - How to get other values based on a slicer value.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ashish_Mathur
Super User
Super User

Hi,

 

Your question is not clear.  Show a sample dataset and the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Ashish, 

 

I've added a picture with data and how I'd like to see the results

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!