DAX to Calculate Measure by Internal Peer

[nmcclary]

I am working with Surgical Site Infection (SSI) Data. I'm wanting to calculate the SSI rate that will filter by physician but also show the average rate for the specialty as well and then for the health system as a whole. I want this measure to change based on the fiscal year or fiscal month selected as well. So, whenever a physician is selected from the slicer, it will calculate the specialty average SSI for the time period that is selected in a slicer as well, without having to select the specialty.

Below is the formula I have used to calculate surgery count, with subsequent formulas of the same type for number of SSIs and SSI rate, just subbing out the first part of the calculate function:

Internal Peer FY Surgery Count =

VAR Specialty = SELECTEDVALUE(Surgeries[SurgeonSpec])

VAR Year = SELECTEDVALUE(zCalendar[FiscalYear])

RETURN

calculate([Surgery Count], FILTER(ALL(Surgeries), Surgeries[SurgeonSpec] = Specialty)

, FILTER(ALL(zCalendar), zCalendar[FiscalYear] = Year))

This calculation partially works. When a provider's name is selected, it will filter to the individual specialty and fiscal year when a fiscal year is selected, exactly what I'm looking for.  However when multiple fiscal years are selected, it no longer works. It also won't change if someone selects the fiscal month, since fiscal year is explicitly stated. Thanks for any help.

[parry2k]

@nmcclary share the sample data and expected out and what is not working, it will help to provide the solution. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[nmcclary]

Here's a sample table of what it would look like for another dataset I have that does not include patient information (below). For this dataset, I would write the code as:

Internal Peer Top Box Responses = 
VAR ProviderSpecialty = SELECTEDVALUE(NRCData[ProviderSpecialty])
VAR SurveyType = SELECTEDVALUE(NRCData[SurveyType])
VAR FiscalYear= SELECTEDVALUE(CalendarTable[FiscalYear]
RETURN
calculate([Average Question ID],
FILTER(all(NRCData), NRCData[ProviderSpecialty] = ProviderSpecialty)
,FILTER(all(NRCData), NRCData[SurveyType] = SurveyType)
, FILTER(all(CalendarTable), CalendarTable[FiscalYear] = FiscalYear)
)

For this report, I have slicers at the top of the page for ProviderName, ProviderSpecialty, SurveyType, FiscalYear.

The goal output would be that whenever they select a ProviderName, this measure will calculate the average ResponseID for the ProviderSpecialty, in this case, Gastroenterology. However, the user could also select a different survey type or fiscal year, and I would still like to know what the average is for that specialty with the filter of survey type or fiscal year.

If I just do the first variable and FILTER statement, it works to filter by specialty correctly, but it averages for the entire date range (2 fiscal years) and does not average for the most recent fiscal year when that filter is selected.

Example data table below:

Provider	SurveyType	ProviderSpecialty	QuestionID	ResponseID	SurveyDate
ABBDS	Emergency Department	GASTROENTEROLOGY	1356	3	10/1/2019
ABBDS	Emergency Department	GASTROENTEROLOGY	1358	3	10/1/2019
ABBDS	Emergency Department	GASTROENTEROLOGY	1494	3	11/1/2019
ABBDS	Emergency Department	GASTROENTEROLOGY	1566	2	11/1/2019
ABBDS	Medical Practice	GASTROENTEROLOGY	138	0	12/1/2019
ABBDS	Medical Practice	GASTROENTEROLOGY	191	1	12/1/2019
ABBDS	Medical Practice	GASTROENTEROLOGY	1017	2	1/1/2020
ABBDS	Medical Practice	GASTROENTEROLOGY	1027	3	1/1/2020
ABBDS	Medical Practice	GASTROENTEROLOGY	1034	4	2/1/2020
ABBDS	Medical Practice	GASTROENTEROLOGY	1035	5	2/1/2020

[nmcclary]

bump

[nmcclary · Accepted solution]

Just in case anyone runs into this in the future, I was able to figure it out and make it work myself:

Internal Peer Top Box Responses = 
VAR ProviderSpecialty = SELECTEDVALUE(SurveyData[ProviderSpecialty])
RETURN
calculate([Top Box Responses]
, FILTER(ALL(SurveyData), SurveyData[ProviderSpecialty] = ProviderSpecialty)
, FILTER(ALL(zCalendar), zCalendar[FiscalYear] IN VALUES(zCalendar[FiscalYear]))
, FILTER(ALL(SurveyData), SurveyData[SurveyType] IN VALUES(SurveyData[SurveyType]))
)