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

[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],
","
)