Forum Discussion
DAX Formula IF statement with Filter
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.
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
)
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.
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.
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.