Show values from one of two columns when other criteria is met

[JenWilson]

I’m trying to create a new column that will collect the value from one of two columns depending on certain circumstances.

So in the first instance (for 3/1) ‘Number’ column does not have 103 in the data, therefore, I would want the ‘FinalGoal’ column to pull the values from the ‘DailyGoalPull2Lines’ column, whereas in the second instance (for 3/7) the ‘Number’ column does have 103 in the data, therefore, I would want the ‘FinalGoal’ column to pull the values from the ‘DailyGoalPull3Lines’ column.

The current formula that I am using is below. This only ends up pulling from the ‘DailyGoalPull3Lines’ column. Is there a way to write a formula that says something like if the ‘number’ column doesn’t have 103 (for said date(s)), then use the ‘DailyGoalPull2Lines’ column, else use the ‘DailyGoalPull3Lines’ column?

FinalGoal = IF([number]=103 && [Qty/Period]=BLANK(),[DailyGoalPull2Lines],[DailyGoalPull3Lines])

[v-jiewu-msft]

Hi @JenWilson ,

Please try the following methods and check if they can solve your problem:

1.Create the simple table.

2.Create the new column to filer.

NUM_103 = IF(
    'Table'[number] = 103,
    [DailyGoalPull3Lines],
    [DailyGoalPull2Lines]
)

3.Select the slicer visual and drag the Date into the visual.

4.Drag the column into the table visual. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vicky_]

This part is the problem: && [Qty/Period]=BLANK()

Your FinalGoal will never pull values from DailyGoalPull2Lines since none of the qty/period is blank. Either remove that from the formula (not sure if there's some part of the problem description that you're missing which requires it), or think about using a combination of AND and OR conditions to get the result you want.

[JenWilson]

@vicky_ taking out  && [Qty/Period]=BLANK() only changes the FinalGoal for the Number column only when it shows 103. What I want the new column to do basically, is look for 103 in the number column (for each date). If there is an occurance of 103 in the Number column, then I would want it to pull the values shown in the DailyGoalPull3Lines column else pull the values from the DailyGoalPull2Lines column.