Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Julbak
Frequent Visitor

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

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:

 

Julbak_0-1710944660857.png

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

 

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

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

View solution in original post

v-zhengdxu-msft
Community Support
Community Support

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:

vzhengdxumsft_0-1711004999916.png

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

vzhengdxumsft_1-1711005070975.png

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

 

vzhengdxumsft_2-1711005151640.png

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

 

vzhengdxumsft_3-1711005207291.png

Finally, add a measure:

 

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

 

The result is as follow:

vzhengdxumsft_4-1711005248941.png

 

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.

View solution in original post

2 REPLIES 2
v-zhengdxu-msft
Community Support
Community Support

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:

vzhengdxumsft_0-1711004999916.png

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

vzhengdxumsft_1-1711005070975.png

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

 

vzhengdxumsft_2-1711005151640.png

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

 

vzhengdxumsft_3-1711005207291.png

Finally, add a measure:

 

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

 

The result is as follow:

vzhengdxumsft_4-1711005248941.png

 

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

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.