topic Re: SUM if it is a number, otherwise CONCATENATEX in DAX Commands and Tips

SUM if it is a number, otherwise CONCATENATEX

Clem212273 — Sat, 10 Dec 2022 15:10:05 GMT

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

Re: SUM if it is a number, otherwise CONCATENATEX

Greg_Deckler — Sat, 10 Dec 2022 17:37:39 GMT

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.

Re: SUM if it is a number, otherwise CONCATENATEX

Clem212273 — Sun, 11 Dec 2022 12:34:40 GMT

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

Re: SUM if it is a number, otherwise CONCATENATEX

Greg_Deckler — Sun, 11 Dec 2022 15:20:56 GMT

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

Re: SUM if it is a number, otherwise CONCATENATEX

Clem212273 — Wed, 14 Dec 2022 12:19:06 GMT

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.

Re: SUM if it is a number, otherwise CONCATENATEX

Clem212273 — Mon, 19 Dec 2022 12:08:15 GMT

Greg_Deckler
Did you see my previous message?