SUM if it is a number, otherwise CONCATENATEX

[Clem212273]

Hello,

What I would like to do:

• To make a pivot table with values that are either numbers (Desired result: SUM) or text (Desired result: CONCATENATEX)

Literally:

• If in column « 21-22_Rangement » ; the value is 'Cotes'; then I sum the values
• If in the column « 21-22_Rangement » ; there is another value ; then I concatenate the texts and separating them with a ", "

My problem: I tried to create a new measure, but I must not give the right conditions.

In the screenshot the formula used is : =CONCATENATEX(VALUES(_102_WALL_LAST_TEXT__2[Valeur]);[Valeur];", ")

Test 1 :

=SWITCH(

TRUE();

_102_WALL_LAST_TEXT__2[21-22_Rangement]="'Cotes'";SUM(_102_WALL_LAST_TEXT__2[Valeur]);

CONCATENATEX(VALUES(_102_WALL_LAST_TEXT__2[Valeur]);[Valeur];", ")

)

But it is indicated to me that one cannot make a sum on texts.

Test 2 :

=SWITCH(

TRUE();

_102_WALL_LAST_TEXT__2[21-22_Rangement]="'Cotes'";SUM(CONVERT(_102_WALL_LAST_TEXT__2[Valeur]),DOUBLE);

CONCATENATEX(VALUES(_102_WALL_LAST_TEXT__2[Valeur]);[Valeur];", ")

)

But it tells me that "Convert" is not a table, variable or function name

I need you’re help please.

Thank’s

Clem212273

[Clem212273]

Hello @Greg_Deckler,

Thanks for your answer.

My problem is that in the same query, I only have data of type "STRING" whereas I need that the data which have information 'Cotes' are of type "DOUBLE" and are summed to have the total.

Currently:
- All data are of type "STRING" and are concatenated and separated by the symbol ", ".

I would like :

- Data of type "STRING" is concatenated and separated by the symbol ", "
- The data that have 'Cotes' information are converted to 'DOUBLE' type and are summed.

To complete my request, here is the table version.

		CURRENTLY	CURRENTLY		WHAT I WANT	WHAT I WANT
21-22_Rangement	23_IFCPROPERTYSINGLEVALUE.Property	VALUES	TYPE		VALUES	TYPE
'Contraintes'	'Contrainte inférieure'	'Niveau 0'	STRING		'Niveau 0'	STRING
'Contraintes'	'Décalage inférieur'	0	STRING		0	STRING
'Contraintes'	'Décalage supérieur'	0	STRING		0	STRING
'Contraintes'	'Hauteur non contrainte'	2,92	STRING		2,92	STRING
'Contraintes'	'Lié au volume'	.F.	STRING		.F.	STRING
'Contraintes'	'Ligne de justification'	'Axe du mur'	STRING		'Axe du mur'	STRING
'Contraintes'	'Limite de pièce'	.T.	STRING		.T.	STRING
'Contraintes'	'Partie inférieure attachée'	.F.	STRING		.F.	STRING
'Contraintes'	'Partie supérieure attachée'	.F.	STRING		.F.	STRING
'Cotes'	'Longueur'	4,1, 10,5, 9,3	STRING		23,90	CONVERT TO DOUBLE AND SUM
'Cotes'	'Surface'	11,97, 29,2, 26,27	STRING		67,44	CONVERT TO DOUBLE AND SUM
'Cotes'	'Volume'	1,2, 2,92, 3,21, 2,89	STRING		10,22	CONVERT TO DOUBLE AND SUM
'Définition de la section'	'Section'	'Vertical'	STRING		'Vertical'	STRING
'Données'	'Bmf_batiment'		STRING			STRING
'Données'	'Bmf_etage'		STRING			STRING
'Données'	'Bmf_type'		STRING			STRING
'Phase de construction'	'Phase de création'	'Nouvelle construction'	STRING		'Nouvelle construction'	STRING
'Structure'	'Structure'	.F.	STRING		.F.	STRING
'Structure'	'Utilisation structurelle'	'Non porteur'	STRING		'Non porteur'	STRING

Thanks

[Greg_Deckler]

@Clem212273 Try this:

Measure 5 = 
    SWITCH( MAX([21-22_Rangement]),
        "'Cotes'", 
            VAR __Path = SUBSTITUTE(MAX([VALUES]), ", ", "|")
            VAR __Len = PATHLENGTH(__Path)
            VAR __Table = 
                ADDCOLUMNS(
                    GENERATESERIES(1, __Len, 1),
                    "__Value", PATHITEM(__Path, [Value], TEXT) + 0
                )
            VAR __Result = SUMX(__Table, [__Value])
        RETURN
            __Result & "",
       MAX([VALUES])
    )

[Clem212273]

@Greg_Deckler 

Thanks for your answer.

I've try, but :

The expression is not valid or appears to be incomplete. " The expression is not valid or appears to be incomplete.

Please review and correct the expression.

The syntax for '__Len' is incorrect. (DAX(

    SWITCH( MAX([21-22_Rangement]),
        "'Cotes'", 
            VAR __Path = SUBSTITUTE(MAX([VALUES]), ", ", "|")
            VAR __Len = PATHLENGTH(__Path)
            VAR __Table = 
                ADDCOLUMNS(
                    GENERATESERIES(1, __Len, 1),
                    "__Value", PATHITEM(__Path, [Value], TEXT) + 0
                )
            VAR __Result = SUMX(__Table, [__Value])
        RETURN
            __Result & "",
       MAX([VALUES])
    )

 )).

".

in attachment, you will find the Excel file, if that can help

https://we.tl/t-jP7RvZS1S7 

Thank you very much.

[Clem212273]

@Greg_Deckler 
Did you see my previous message?

[Greg_Deckler]

@Clem212273 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.