SWITCH

[jamieham]

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 adding Negative GL. How do I get it to ignore cells that are blank or is there a better way of doing it?

Gainline Prev                   Gainline

0                                      Negative GL

0                                      Negative GL

1                                      Positive GL

                                        Negative GL (dont want this to show)

0                                      Negative GL

1                                      PositiveGL

Gainline = SWITCH(TRUE(),default[Gainline Prev]=1,"Positive GL",default[Gainline Prev]=0,"Negative GL")

[Anonymous]

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

[jamieham]

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

[amitchandak]

@jamieham ,

You can create a measure like

calculate([Meausre], filter(Table, not(isblank(Table[Gainline Prev]))))

Or you can use visual level or page filter Table[Gainline Prev] <> Blank ()

[Anonymous]

HI @jamieham 

In the filter pane for the Gainline Prev, you can say to ignore blank as below