Filter a table with the StDev of a UNIONed column within a Var Table

[lundande]

I am trying to find the StDev of values for a filter, but I need to merge the data from two columns in my table first.
This is what I have tried, but I cannot figure out how to merge columns in a Variable, and then reference one of those columns in a filter,I know other ways to do it, I am just trying to figure it out in a DAX formula for my own satisfaction at this point.  Any help would be appreciated!

AllCitys =    
VAR   VARTable = FILTER(

UNION(

SELECTCOLUMNS(ShipmentInfo,"City",ShipmentInfo[City 1],"State",ShipmentInfo[State 1],"Number",ShipmentInfo[Number 1]),

SELECTCOLUMNS(ShipmentInfo,"City",ShipmentInfo[City 2],"State",ShipmentInfo[State 2],"Number",ShipmentInfo[Number 2]))

,[Number] <> 9999 && [Number] >=0)

 RETURN
UNION(
SELECTCOLUMNS(ShipmentInfo,"City",ShipmentInfo[City 1],"State",ShipmentInfo[State 1],"Number",ShipmentInfo[Number 1]),
SELECTCOLUMNS(ShipmentInfo,"City",ShipmentInfo[City 2],"State",ShipmentInfo[State 2],"Number",ShipmentInfo[Number 2]))
, [Number] <= STDEV.P([VARTABLE[Number] *3)

ShipmentInfo Table:

City 1	State 1	Number 1	City 2	State 2	Number 2
NewYork	NY	1	Boston	MA	4
Minneapolis	MN	7	Chicago	IL	17
Boston	MA	5	NewYork	NY	6
Chicago	IL	12	Minneapolis	MN	60

New Table: 

City	State	Number
NewYork	NY	1
Minneapolis	MN	7
Boston	MA	5
Chicago	IL	12
Boston	MA	4
Chicago	IL	17
NewYork	NY	6

[lundande]

Thank you!

[lbendlin · Accepted solution]

" RETURN
UNION"

You are missing the second Filter.  But even then you can only return this into a calculated table.  A measure can only be a scalar value.

Here is the DAX that results in a calculated table:

AllCitys = 
VAR   VARTable = FILTER(
UNION(
SELECTCOLUMNS(ShipmentInfo,"Number",ShipmentInfo[Number 1]),
SELECTCOLUMNS(ShipmentInfo,"Number",ShipmentInfo[Number 2]))
,[Number] <> 9999 && [Number] >=0 )

RETURN
FILTER(UNION(
SELECTCOLUMNS(ShipmentInfo,"City",ShipmentInfo[City 1],"State",ShipmentInfo[State 1],"Number",ShipmentInfo[Number 1]),
SELECTCOLUMNS(ShipmentInfo,"City",ShipmentInfo[City 2],"State",ShipmentInfo[State 2],"Number",ShipmentInfo[Number 2]))
, [Number] <= STDEVX.P(VARTable,[Number]))