Regroup in one line a TEXT value according to a list of lines (which function(s))?

[Julbak]

Hello Community,

I am searching a solution to regroup a set of lines into one line in Power BI using DAX.

Example of one case:

How it works:

For 1 passage (in this case the number #7 for the column "passage") I have got a mapping of cutting explained in the "End point" column.

The idea is to regroup automatically in one line the "cutting map" based on one passage.

For example here: 

The line (TEXT) I want to get is: "2x87 + 22x66 + 10 x 87 + 12 x 66"

The order is based by the End Point column (that represents from left to right) the position of the different rolls with the "Laize" that is the width actually. 

Example: 2x87 means 2 rolls of 87mm width then 22 rolls of 66mm witdh then... (from let to right) but row to row in the table.

I tried to use different fonctions to get this line but I have never been accustomed of that in Power Bi so I am freezing 😄 

Thanks for your advice with what kind of DAX formula I can use to get this 🙂

Julien

[lbendlin · Accepted solution]

Usually CONCATENATEX can do that.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[v-zhengdxu-msft · Accepted solution]

Hi @Julbak 

Thanks for the reply from @lbendlin , please allow me to provide detailed description:
First of all, I create a set of sample data:

Then add a index(from 1) column in power query:

Then add a calculate column:

Column =
VAR _PerviousIndex =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Index]
                = EARLIER ( 'Table'[Index] ) + 1
        )
    )
VAR _PerviousLaize =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[Laize] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Index] = _PerviousIndex )
    )
RETURN
    IF ( 'Table'[Laize] <> _PerviousLaize, 'Table'[Index] )

Next, create a new table:

Table 2 =
VAR _newTable =
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[Column] <> BLANK () ),
        'Table'[Laize],
        'Table'[Column]
    )
RETURN
    SELECTCOLUMNS (
        _newTable,
        "Laize1", [Laize],
        "Count",
            (
                [Column]
                    - MAXX ( FILTER ( _newTable, [Column] < EARLIER ( 'Table'[Column] ) ), [Column] )
            ) & "*" & [Laize]
    )

Finally, add a measure:

Measure = CONCATENATEX('Table 2','Table 2'[Count],"+")

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

Usually CONCATENATEX can do that.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...