Parsing a column base on filter values

[Rigolleto]

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 statement to achive this request

I did ask about and I got an answere but, I have a new issue and it is related with the Matriz the CONCATENATEX read the whole data grid but I need to break and concatenate as the screenshot showns

Thanks

[Rigolleto]

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 statement to achive this request

I did ask about and I got an answere but, I have a new issue and it is related with the Matriz the CONCATENATEX read the whole data grid but I need to break and concatenate as the screenshot showns

Thanks

[v-lili6-msft · Accepted solution]

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

[Greg_Deckler]

Perhaps:

Column = 
  VAR __Values = 
    CONCATENATEX(
      DISTINCT(
        FILTER(
          ALL('Table'),
          [COLUMN1] = EARLIER([COLUMN1)
        )
      ),
      [COLUMN2],
      "-"
    )
RETURN
  __Values

[PaulDBrown]

@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:

[amitchandak]

So if display column1 along with this formula you will get output

Measure = CONCATENATEX (table,table[Column2])