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

Hi ,

 

Does any have a solution or suggestions for this ? 

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