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

 

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

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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

    Measure = CONCATENATEX (table,table[Column2])

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

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Perhaps:

     

    Column = 
  VAR __Values = 
    CONCATENATEX(
      DISTINCT(
        FILTER(
          ALL('Table'),
          [COLUMN1] = EARLIER([COLUMN1)
        )
      ),
      [COLUMN2],
      "-"
    )
RETURN
  __Values
  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    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