How Concatenate Text by Category

[gomezc73]

Hi Experts,

I have the following issue.

I have in a table the explanations of variations by accounts.

Something like these:

Lead	Account	Sep 23	Sep22	VARIATION	Explanation
CASH	C01	2000	1000	1000	Variation due monetary reconversion FROM USD to EUR
CASH	C02	23500	25000	-1500	Reduction on the cash deposits pertaining to bookings due to the change on the credit card processor
TRADE	T01	5000	1000	4000	credit card settlements are now being collected in the corporate bank
TRADE	T02	3400	1000	2400	Variations due a revaluation in Suisse bank
OTHER	I01	2000	1000	1000	Funds in this account are used to pay the payroll and any other payroll related items pertaining to the employees under this company
OTHER	I02	10000	1000	9000	Increase mainly driven by timing on the collection
OTHER	I03	2000	1000	1000	Payments for medical claims and other benefic

Now, In the powerBi report i need a slicer to select a LEAD (CASH/TRADE/OTHER) and show all the explanations concatenated and numbered.

I know that i can use Concatenex with summarize but i can't get the output requested.

Can you help me?

I need Something like this:

Trade	1 credit card settlements are now being collected in the corporate bank. 2 Variations due a revaluation in Suisse bank
OTHER	1 Funds in this account are used to pay the payroll and any other payroll related items pertaining to the employees under this company 2 Increase mainly driven by timing on the collection 3 Payments for medical claims and other benefic

[Greg_Deckler]

@gomezc73 Try this. PBIX attached below signature.

Measure = 
    VAR __PathText = CONCATENATEX('Table', [Explanation], "|")
    VAR __Table = 
        ADDCOLUMNS(
            GENERATESERIES(1, COUNTROWS('Table'), 1),
            "__Text", PATHITEM(__PathText, [Value])
        )
    VAR __Result = CONCATENATEX(__Table, [Value] & " " & [__Text] & UNICHAR(10) & UNICHAR(13))
RETURN
    __Result

[gomezc73]

Hi, i can't open the PBI because i have a prior version installed, can you please send me an screenshot of the formula?.. i reaaly appreciatte

[Greg_Deckler · Accepted solution]

@gomezc73 I posted the formula in the original reply. All you should have to do is replace 'Table' with your actual table name assuming that your columns names are what you posted.

[gomezc73]

Hi Greg, i have a lithe issue maybe you can help me with a variation of the measure..

The final user told me that he only explain variations greater of 6%, then i have some accounts with the explanation field in blanks..

I tried to filter only explanations not blanks, but then the sum of amounts also filter it and it is incorrect..

There is a way of modify this measure to only concatenate explanations Non blanks without filter the amounts?.

By example( see the below dada) the account I02 haven't explanation. then i need concatenate only the explanation of the accounts I01, I03, I04, but the Sum of the variations must be 10.980(considerand the amount with no explanation).

When i filter only explanations Non Blanks the amount of variations is 11.000.

Can you please help me?

Lead	Account	23-Sep	22-Sep	VARIATION	Explanation
OTHER	I01	2000	1000	1000	Funds in this account are used to pay the payroll and any other payroll related items pertaining to the employees under this company
OTHER	I02	10980	11000	-20
OTHER	I03	10000	1000	9000	Increase mainly driven by timing on the collection
OTHER	I04	2000	1000	1000	Payments for medical claims and other benefic

[Greg_Deckler]

@gomezc73 Sure, you can filter the blanks in the measure:

Explanation Measure = 
    VAR __PathText = CONCATENATEX(FILTER('Table', [Explanation] <> BLANK()), [Explanation], "|")
    VAR __Table = 
        ADDCOLUMNS(
            GENERATESERIES(1, COUNTROWS('Table'), 1),
            "__Text", PATHITEM(__PathText, [Value])
        )
    VAR __Result = CONCATENATEX(__Table, [Value] & " " & [__Text] & UNICHAR(10) & UNICHAR(13))
RETURN
    __Result

[gomezc73]

Hi,

 I tried but it still it show at the end blanks lines numbered.. See the image

[gomezc73]

Amazing!!. worked perfect!!.

thank you!!  

[CoreyP]

When you say numbered, what does this number mean? Is it ordered in any particular way? Or represent a rank based on the number of times that explanation occurs?

[gomezc73]

Hi, it is only a number 1, 2, 3, 4  or a,b,c,d..  it is only to identify that it is a separate explanatations.

By example, the first explanation must be '1', the second explanations must have a '2' etc..

thank you

[CoreyP]

Oh, gotcha. @Greg_Deckler 's solution is great. For future iterations, if the explanations are not free text, but selected from a list of standard available options, counting their frequency and ranking them might provide some useful insights. Just a thought.