Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

How to remove leading commas from the concatenated String

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 t...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
7 years ago

Anonymous 

 

Hi, try with this:

 

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

Regards

 

Victor

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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