cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AW1976NOVA
Post Patron
Post Patron

Need to update DAX measures to consider a page filter (complex problem)

Hi,

 

I have three measures that are working correctly...although I now need to update them in order to consider one of the page filters I have added to the page.

 

Here are my current measures that I need to update:

7 Day Readmission Rate Delta v2 =
IF (
SELECTEDVALUE ( 'Successful Intervention Passthrough'[Result] ) <> "Total",
CALCULATE (
SUM ( 'Post Call to Home Append'[7 Day Readmit flag] ) / SUM ( 'Post Call to Home Append'[EASY COUNT] ),
FILTER (
ALL ( 'Post Call to Home Append' ),
[Successful Intervention Column] = SELECTEDVALUE ( 'Successful Intervention Passthrough'[Result] )
)
),
CALCULATE (
SUM ( 'Post Call to Home Append'[7 Day Readmit flag] ) / SUM ( 'Post Call to Home Append'[EASY COUNT] ),
FILTER ( ALL ( 'Post Call to Home Append' ), [Successful Intervention Column] IN { "Yes","No"}
)

))

 

14 Day Readmission Rate Delta v2 =
IF (
SELECTEDVALUE ( 'Successful Intervention Passthrough'[Result] ) <> "Total",
CALCULATE (
SUM ( 'Post Call to Home Append'[14 Day Readmit flag] ) / SUM ( 'Post Call to Home Append'[EASY COUNT] ),
FILTER (
ALL ( 'Post Call to Home Append' ),
[Successful Intervention Column] = SELECTEDVALUE ( 'Successful Intervention Passthrough'[Result] )
)
),
CALCULATE (
SUM ( 'Post Call to Home Append'[14 Day Readmit flag] ) / SUM ( 'Post Call to Home Append'[EASY COUNT] ),
FILTER ( ALL ( 'Post Call to Home Append' ), [Successful Intervention Column] IN { "Yes","No"}
)

))
 
30 Day Readmission Rate Delta v2 =
IF (
SELECTEDVALUE ( 'Successful Intervention Passthrough'[Result] ) <> "Total",
CALCULATE (
SUM ( 'Post Call to Home Append'[30 Day Readmit flag] ) / SUM ( 'Post Call to Home Append'[EASY COUNT] ),
FILTER (
ALL ( 'Post Call to Home Append' ),
[Successful Intervention Column] = SELECTEDVALUE ( 'Successful Intervention Passthrough'[Result] )
)
),
CALCULATE (
SUM ( 'Post Call to Home Append'[30 Day Readmit flag] ) / SUM ( 'Post Call to Home Append'[EASY COUNT] ),
FILTER ( ALL ( 'Post Call to Home Append' ), [Successful Intervention Column] IN { "Yes","No"}
)

))

 

As you can see in the following image, these three measures are being used as the Column Values of the visual.  You'll also notice that I am using the field Result as the Shared Axis:

Capture.PNG

 

These measures are all calculating correctly for all Groups.  But I now need to have a page drop down filter where I can select a specific group and have this bar chart visual update based on the selection.  As you can see in the photo above, I have added the filter. However, when I change a selection it is not interacting with the visual.

 

Here is an image of the relationship map:

Capture2.PNG

 

- The Employer Group filter is populated with the Group Name field in the EmployerGroups table

- The EmployerGroups table has a one to many relationship with the Post Call to Home Append table based on the Group ID column which is present in both tables

- I needed to create the Successful Intervention Passthrough table in order to assist in being able to create the Totals result...so I could plot Total, Yes and No results on the same table.

 

But now, with the three formulas that I mentioned earlier...I have no idea how to update them (how to re-write them) so they will interact with the Employer Group filter...because the Employer Group filter (that is populated by the field Group Name in the table Employer Groups) has no direct relationship with the Successful Intervention Passthrough table.

 

Can you PLEASE assist me here????

 

 

 

 

Thank you,

Andrew

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

Hi, @AW1976NOVA 

ALL function ignores all filters, regardless of where they are coming from. In contrast, the ALLSELECTED function only ignores filters that are coming from the inner query.

Take a try  to replace  ALL function with ALLSELECTED function.

 

Best Regards,
Community Support Team _ Eason

View solution in original post

1 REPLY 1
v-easonf-msft
Community Support
Community Support

Hi, @AW1976NOVA 

ALL function ignores all filters, regardless of where they are coming from. In contrast, the ALLSELECTED function only ignores filters that are coming from the inner query.

Take a try  to replace  ALL function with ALLSELECTED function.

 

Best Regards,
Community Support Team _ Eason

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors