topic Re: Regroup in one line a TEXT value according to a list of lines (which function(s))? in DAX Commands and Tips

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

Julbak — Wed, 20 Mar 2024 14:30:17 GMT

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

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

lbendlin — Wed, 20 Mar 2024 22:17:19 GMT

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-Forum/ba-p/963216

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

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

Anonymous — Wed, 27 Mar 2024 01:38:57 GMT

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.