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

[Anonymous]

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:

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:

- 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

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

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