How to remove leading commas from the concatenated String

[Anonymous]

Hi Team,

I have a concatenated text string like ",176.22.191.10,None,254.255.244.0" . This is a concantenated value comprises of certain values. Customer doesnt want to see the Leading comma at the begining of the Value. Commas can be there between two values , but not at begining. 

Could you please help me to remove it ? I tried the below formulae , but it didnt worked for this scenario. 

Comma Removal = IF(LEFT([List of Prod_Gateway_IP_Address values],1)=",",SUBSTITUTE([List of Prod_Gateway_IP_Address values],",",""),[List of Prod_Gateway_IP_Address values])

[SharathS03]

Hi All,

Leading commas are the result of the BLANK values table values which we are trying to concatenate. For example if I am trying to concatenate values in a table column which contains BLANK, the first value in the concatenated string is blank because of which we dont see the value but just the "," (comma) after that. The solution for this is to filter out blank in the first step.

Quarters

Q1

Q2

Q3

If I concatenate this Quarters column values, using the formula  

CONCATENATEX( VALUES (Table[Quarters]),Table[Quarters],",") 

It will return ,Q1,Q2,Q3

The easiest way to tackle this is to update the formula to EXCLUDE Blank values

CONCATENATEX( CALCULATETABLE(VALUES (Table[Quarters]),Table[Quarters]<>BLANK()),Table[Quarters],",") 

 This will return Q1,Q2,Q3

[AlB]

Hi @Anonymous 

Try this:

Comma Removal =
IF (
    LEFT ( [List of Prod_Gateway_IP_Address values], 1 ) = ",",
    MID (
        [List of Prod_Gateway_IP_Address values],
        2,
        LEN ( [List of Prod_Gateway_IP_Address values] ) -1
    ),
    [List of Prod_Gateway_IP_Address values]
)

[Anonymous]

@AlB ,

Thanks for the reply. I tried your method and it is giving the same results of my method . Please find the screen shot below . It is removing the Comma, but giving results in seperate rows . So i have concatenated "Comma Removal" again and it started giving commas again. 

[AlB]

@Anonymous 

I don't understand what you are showing in that table or what you are trying to do. You'll have to explain it in detail and/or share the pbix. Otherwise I cannot help. Is that a table visual? hat fields are involved? From what table(s)? what is the structure of those tables?  Is 'Comma removal' a measure? and [List of Prod_Gateway_IP_Address values]? I thought they were both calculated columns but it doesn't seem so.  

[Anonymous]

Hi AIb,

Sorry for not explaining it in detail. I explained it through screen shots and detail information below. 

Firstly , the screen shot i have shown in previous discussion was the table ( Report output) . I have attached the screenshot explaining everything in Detail . Please let me know in case if anything else is required.  As Companys data is there, I couldnt able to share pbix file. 

[Anonymous]

Hi ,

Does any have a solution or suggestions for this ? 

[Anonymous]

Hi Team,

I have attached DAX Code for the same and resulted output which it gives :

List of Target IP Address values =
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Query1'[Target IP Address])
VAR __MAX_VALUES_TO_SHOW = 300
RETURN
IF(
__DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
CONCATENATE(
CONCATENATEX(
TOPN(
__MAX_VALUES_TO_SHOW,
VALUES('Query1'[Target IP Address]),
'Query1'[Target IP Address],
ASC
),
'Query1'[Target IP Address],
", ",
'Query1'[Target IP Address],
ASC
),
", etc."
),
CONCATENATEX(
VALUES('Query1'[Target IP Address]),
'Query1'[Target IP Address],
",",
'Query1'[Target IP Address],
ASC
)
)

Resulted Output : 

,10.0.0.1,169.254.2.129,169.254.2.130,169.254.2.187,169.254.2.244,169.254.2.98,172.29.241.43,172.29.241.45,172.29.241.46

Desired Output : 

10.0.0.1,169.254.2.129,169.254.2.130,169.254.2.187,169.254.2.244,169.254.2.98,172.29.241.43,172.29.241.45,172.29.241.46

[Vvelarde]

@Anonymous 

Hi, try with this:

ConcatenatedValues =
CONCATENATEX (
    FILTER ( Table1; Table1[Values] <> BLANK () ),
    Table1[Values],
    ","
)

Regards

Victor

[Anonymous]

Thanks Victor , 

I did tried your suggestion and I see an output like this . I am doing some thing wrong , but I am new to PBI. 

Measure : ConcatenatedValues =
CONCATENATEX (
FILTER (Query1,Query1[Target IP Address]<> BLANK () ),
Query1[Target IP Address],
","
)