Forum Discussion
SWITCH
I'm trying to create a new column using the SWITCH function and I'm using the DAX formula below. The issue I have is if there is a blank for Gainline Prev the DAX formula is reading it as a 0 and add...
Hi jamieham ,
According to my understanding, you want to use switch() to create a new column and filter the blank row, right?
You could use the following formula:
Gainline =
VAR _blank =
ISBLANK ( SELECTEDVALUE ( Default[Gainline Prev] ) )
RETURN
SWITCH (
TRUE (),
SELECTEDVALUE ( Default[Gainline Prev] ) = 1
&& _blank = FALSE (), "Positive GL",
SELECTEDVALUE ( Default[Gainline Prev] ) = 0
&& _blank = FALSE (), "Negative GL"
)My visualization looks like this:
Is the result what you want? If you have any questions, please upload some data samples and expected output.
Please do mask sensitive data before uploading.
Best Regards,
Eyelyn Qin
I've attached a small sample of the data. I want to create a new column called Gainline that looks at Previous GL and adds Positive GL if the number is 1 and Negative GL if the number is 0. The issue I've had is that it's adding Negative GL if the cell is blank in Previous GL.
I want to add a column as per the attached table that is highlighted in red.
Hope that all makes sense.
Regards
Jamie
| Team | Team ID | Play Num | Set Num | PS Timestamp | PS Endstamp | Home Team | Away Team | Match Time | Half | Score_Advantage | Description | Metres | X_Coord | X_Coord End | Y_Coord | Y_Coord End | Action Type | Action Result | Gainline Next | Gainline Prev | Gainline |
| Team 1 | 500 | 3 | 103 | 7246 | 7250 | Team 1 | Team 2 | 7956 | 2 | 7 | Ruck | 0 | 82 | 0 | 8 | 0 | NA | Won Outright | 1 | 0 | Negative GL |
| Team 1 | 500 | 4 | 103 | 7257 | 7261 | Team 1 | Team 2 | 8008 | 2 | 7 | Ruck | 0 | 87 | 0 | 44 | 0 | NA | Won Outright | 1 | 1 | Positive GL |
| Team 1 | 500 | 5 | 103 | 7265 | 7269 | Team 1 | Team 2 | 8016 | 2 | 7 | Ruck | 0 | 89 | 0 | 58 | 0 | NA | Won Outright | 1 | 1 | Positive GL |
| Team 3 | 8850 | 1 | 1 | 4 | 12 | Team 3 | Team 4 | 10 | 1 | 0 | Possession | 13 | 80 | 93 | 66 | 68 | Start Set 50m Restart Retained | End Set Own Lineout | |||
| Team 3 | 8850 | 6 | 2 | 44 | 101 | Team 3 | Team 4 | 131 | 1 | 0 | Possession | 3 | 92 | 95 | 68 | 53 | Lineout | End Pen Won | |||
| Team 4 | 3000 | 2 | 4 | 164 | 178 | Team 3 | Team 4 | 257 | 1 | 0 | Possession | -1 | 5 | 4 | 0 | 11 | Start Set Lineout Steal | End Scrum |