SWITCH

Question

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?&nbsp;Gainline Prev&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Gainline0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Negative GL0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Negative GL1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Positive GL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Negative GL (dont want this to show)0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Negative GL1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PositiveGL&nbsp;Gainline = SWITCH(TRUE(),default[Gainline Prev]=1,"Positive GL",default[Gainline Prev]=0,"Negative GL")&nbsp;&nbsp;

anonymous · Answer

HI&nbsp;jamieham&nbsp;&nbsp;In the filter pane for the Gainline Prev, you can say to ignore blank as below&nbsp;

amitchandak · Answer

jamieham , 
You can create a measure like
calculate([Meausre], filter(Table, not(isblank(Table[Gainline Prev]))))
&nbsp;
Or you can use visual level or page filter Table[Gainline Prev] &lt;&gt; Blank ()

anonymous · Answer

Hi&nbsp;jamieham&nbsp; ,According to my understanding, you want to use switch() to create a new column and filter the blank row, right?&nbsp;You could use the following formula:Gainline =
VAR _blank =
    ISBLANK ( SELECTEDVALUE ( Default[Gainline Prev] ) )
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( Default[Gainline Prev] ) = 1
            &amp;&amp; _blank = FALSE (), "Positive GL",
        SELECTEDVALUE ( Default[Gainline Prev] ) = 0
            &amp;&amp; _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.&nbsp;Best Regards,Eyelyn Qin

jamieham · Answer

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.&nbsp;I want to add a column as per the attached table that is highlighted in red.&nbsp;Hope that all makes sense.&nbsp;Regards&nbsp;Jamie&nbsp;TeamTeam IDPlay NumSet NumPS TimestampPS EndstampHome TeamAway TeamMatch TimeHalfScore_AdvantageDescriptionMetresX_CoordX_Coord EndY_CoordY_Coord EndAction TypeAction ResultGainline NextGainline PrevGainlineTeam 1500310372467250Team 1Team 2795627Ruck082080NAWon Outright10Negative GLTeam 1500410372577261Team 1Team 2800827Ruck0870440NAWon Outright11Positive GLTeam 1500510372657269Team 1Team 2801627Ruck0890580NAWon Outright11Positive GLTeam 3885011412Team 3Team 41010Possession1380936668Start Set 50m Restart RetainedEnd Set Own Lineout&nbsp;&nbsp;Team 388506244101Team 3Team 413110Possession392956853LineoutEnd Pen Won&nbsp;&nbsp;Team 4300024164178Team 3Team 425710Possession-154011Start Set Lineout StealEnd Scrum&nbsp;&nbsp;&nbsp;

Forum Discussion

SWITCH

4 Replies

Related content

Alternative to SWITCH ()?

Switch -Containsstring

Order switching mid workflow

Using Switch

Switch issues

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