DAX Formula IF statement with Filter

[jlafond]

Hey all, I am having difficulty writing a successful DAX formula, and wondered if someone could assist.

TableA and TableB are joined many-to-one in both directions on the field CampaignID (whole number).

TableA

CampaignID     PhaseID

1000                 1

1500                 2

2000                 3

2500                 4

3000                 5

3500                 6

4000                 7

4500                 8

5000                 9

5500                 10

CampaignID (whole number)

PhaseID (whole number)

TableB

Name               CampaignID     Raffles

Jane Doe          4500                 2000

Jane Doe          5000                 16000

Jane Doe          2000                 12000

Jake Doe          1500                 7000

Jake Doe          2000                 3000

Joe Smith         5000                 1000

Joe Smith         4500                 9000

Jill Smith          3000                 6000

Name (text)

CampaignID (whole number)

Raffles (whole number)

Desired outcome:  Create a new measure or column in TableB (I'm unsure which will work better, but I don't care as long as I get the correct values). The new field will be named RafflesCapped. The folks who enter the campaigns in TableB are to have their raffles capped for each campaign, though that capped number is different for the campaigns.

Campaign Cap Rule:

If the TableA.PhaseID value is less than 8 and the SUM of the TableB.Raffles is greater than 6000, then I want the RafflesCapped value to be 6000, else return the SUM of the TableB.Raffles value.

If the TableA.PhaseID value is greater than or equal to 8 and the SUM of the TableB.Raffles is greater than 8000, then I want the RafflesCapped value to be 8000, else return the SUM of the TableB.Raffles value.

This is being grouped by the Name field in a table with the new RafflesCapped value. 

[Greg_Deckler]

@jlafond Maybe:

RafflesCapped Column in TableB =
  VAR __PhaseID = RELATED('TableA'[PhaseID])
  VAR __CampaignID = 'TableB'[CampaignID]
  VAR __Sum = SUMX(FILTER(ALL('TableB'),[CampaignID] = __CampaignID),[Raffles])
RETURN
  SWITCH(TRUE(),
    __PhaseID < 8 && __Sum > 6000,6000,
    __PhaseID >= 8 && __Sum > 8000,8000,
    __Sum
  )

[jlafond]

This solution seems to be close. It is identifying the correct records to apply the 8000 or 6000 capped number, but it's returning that capped 8000 or 6000 value for every name on the list, even if their total Raffles was less than that.

[jlafond]

I'm ultimately trying to filter by the Name & CampaignID to get the correct RafflesCapped value.

Expected results from the sample table data in the original post:

Name                CampaignID       RafflesCapped

Jane Doe            2000                   6000

Jane Doe            4500                   2000

Jane Doe            5000                   8000

Jake Doe            1500                   6000

Jake Doe            2000                   3000

Joe Smith           4500                   8000

Joe Smith           5000                   1000

Jill Smith            3000                   6000

Hopefully that helped clarify any confusion.

[jlafond · Accepted solution]

Never mind. I was able to modify your solution a bit to make it work for me. All good now, thanks!

I had to add one more VAR for the second field I wanted to filter by (Name). I then put your SUMX formula into a CALCULATE formula with multiple filters.