Forum Discussion

Rigolleto's avatar
Rigolleto
Icon for Resolver I rankResolver I
6 years ago
Solved

Parsing a column base on filter values

    Hi There,    Just to ask about the way to take one column and create a new one with the values of the first column separated by DASHES?   Any one can advise me about the DAX state...
Tips and Tricks
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    HI  Rigolleto 

    If you want to create a calculated column, and just want to show once for each COLUMN1, you need to add an index in edit queries, then add a calculated column as below:

    output1 = 
var _column1='Table'[COLUMN1]
var _firstrow= CALCULATE(MIN('Table'[Index]),FILTER('Table','Table'[COLUMN1]=_column1))
return
IF('Table'[Index]=_firstrow,   CONCATENATEX( FILTER( 'Table', [COLUMN1] = EARLIER([COLUMN1]) ), [COLUMN2] ))

    otherwise, you could just use this simple formula:

    output2 = 
 CONCATENATEX( FILTER( 'Table', [COLUMN1] = EARLIER([COLUMN1]) ), [COLUMN2] )

    Result:

     

    Regards,

    Lin

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
6 years ago

Rigolleto 

 

Try:

 

 

ConcatenateX = 
VAR CALC = CONCATENATEX(Table1; Table1[SUBCAT]; "-")
RETURN
IF(ISINSCOPE(Table1[CAT]); CALC; BLANK())

 

 

Which gets you:

 

Or, if you want to include the SUBCAT column in your table:

 

 

ConcatenateX with SUBCAT = 
VAR calc = CALCULATETABLE(VALUES(Table1[SUBCAT]); ALLEXCEPT(Table1; Table1[CAT]))
RETURN
IF(ISINSCOPE(Table1[CAT]); CALCULATE(CONCATENATEX(calc; Table1[SUBCAT]; "-")); BLANK())

 

 

Which gets you:

 

Or, if you want to show what other SUBCATS are in the same CAT:

 

 

Other SUBCAT in CAT = 
VAR calc = CALCULATETABLE(VALUES(Table1[SUBCAT]); ALLEXCEPT(Table1; Table1[CAT]))
VAR vals = VALUES(Table1[SUBCAT])
VAR newt = EXCEPT(calc; vals)
RETURN
CONCATENATEX(newt; Table1[SUBCAT]; "-")

 

 

Which gives you: