DAX calculates values for fields not included in original table

[davidz106]

I am having a problem with DAX formula. 

I have two tables with following relations

EVS                        ALL_IDS

ID           *:1            ID

value    

I want to show a table visualization with ID (All_IDs) and a following measures based on EVS table:

average(value)

stdev.s(value)

custom measure named AA_ID

AA_ID =
IF (
    [Average_ID] < 88.5,
    ( 88.5 - [Average_ID] ) + 1.4 * [STDEV_ID],
    IF (
        [Average_ID] > 91.5,
        ( [Average_ID] - 91.5 ) + 1.4 * [STDEV_ID],
        [STDEV_ID] * 1.4
    )
)

Measure AA_ID calculates for all the IDs in EVS table but also for the IDs (ALL_IDS) that are not present in EVS table which is unwanted behavior. Value 88.5 is shown for those IDs. I figured out that DAX take 0 as average and stdev in those cases instead of null and still calculates AA_ID (which is 88.5 in case of other two measures being 0).

How can I filter those out (I want the calculation to be performed only for my EVS table). At the same time (because of other graphs) I still want to be able to filter by  ID (All_IDs).

[amitchandak]

@davidz106 . Try like

Calculate( IF (
[Average_ID] < 88.5,
( 88.5 - [Average_ID] ) + 1.4 * [STDEV_ID],
IF (
[Average_ID] > 91.5,
( [Average_ID] - 91.5 ) + 1.4 * [STDEV_ID],
[STDEV_ID] * 1.4
)
), not(isblank(EVS([ID])) )

[davidz106 · Accepted solution]

I found a solution, a very ugly one so if anybody has better idea I would aprreicate it. 

I defined a new measure:

IF ([AA_ID] = 88.5, blank (), [AA_ID])

[davidz106]

This does not seem to work. Nothing changes  if I use:

AA_ID =
CALCULATE (
    IF (
        [Average_ID] < 88.5,
        ( 88.5 - [Average_ID] ) + 1.4 * [STDEV_ID],
        IF (
            [Average_ID] > 91.5,
            ( [Average_ID] - 91.5 ) + 1.4 * [STDEV_ID],
            [STDEV_ID] * 1.4
        )
    ),
    NOT ( ISBLANK ( EVS[ID] ) )
)

or EVS[value] for that matter. I also tried EVS[value]>0 in second to last row with no success.