Filtered data shows wrong values on measure

[lavnyak]

Hi Everyone,

I have a table named NDI as below. Created a Measure to derive another table (NDI_DISTINCT) out of it by taking distinct values.

NDI Table:

RGN_CD

LINE_OF_BUSINESS

Spoken_language_Key

Race_Key

Gender_Key

CAR_BUCKET

SCH_TELE_CT

VIDEO_CT

INPRSN_CT

URG_CARE_CT

SPECIALTY_GRP

Age_Key

START_YEAR_MONTH

END_YEAR_MONTH

Average Membership

Derived table:

NDI_DISTINCT =

 DISTINCT(

    SUMMARIZE (

        'NDI',

       NDI[Region_KEY],NDI[Line Of Business_KEY],

       NDI[CAR_BUCKET],

       NDI[Spoken Language_KEY],

       NDI[Race_KEY],

       NDI[Gender_KEY],

       NDI[Age],

     "Average Membership",  MAX(NDI[Average Membership])

    )

And then I have created a new key column on both tables using the below Measure:

Key =

NDI[Region_KEY]  &

NDI[Line Of Business_KEY] &

NDI[Spoken Language_KEY]  &

NDI[Race_KEY]             &

NDI[Gender_KEY] &

NDI[CAR_BUCKET] &

NDI[Age_KEY]

and then joined those 2 tables.(removed nulls then shows 1 to Many)

userate = volume(coming from NDI) / AVG_MEM(coming from NDI_DISTINCT) * 1000

For AVG_MEM = 

CALCULATE(

SUM(NDI_DIstinct[Average Membership])

,ALLEXCEPT(NDI_DIstinct,NDI_DIstinct[Region],NDI_DIstinct[CAR_BUCKET])

)

It was working fine before the refresh and when I refreshed for the latest quarter it is showing wrong AVG_MEM values when I apply filters like Age, specialty group etc.
Could that be beacause of the Key column that I'm using to relate ? Is there any other way to calculate AVG_MEM instaed of deriving a new table and calculating there?  Any help is appeciated. Thank you!!
@amitchandak 
@Ashish_Mathur 

[lavnyak]

Thanks for the response @amitchandak . I was able to find the issue.  Thank you!!

[amitchandak]

@lavnyak , Calculated column will become static and will not respond to slicers.
Either join the new table with dimension that are filtering or try a measure like

For AVG_MEM =
var NDI_DISTINCT =
DISTINCT(
SUMMARIZE (
'NDI',
NDI[Region_KEY],NDI[Line Of Business_KEY],
NDI[CAR_BUCKET],
NDI[Spoken Language_KEY],
NDI[Race_KEY],
NDI[Gender_KEY],
NDI[Age],
"Average Membership", MAX(NDI[Average Membership])
)
return
CALCULATE(
SUMX(NDI_DIstinct, [Average Membership])
,ALLEXCEPT('NDI','NDI'[Region],'NDI'[CAR_BUCKET])
)