Forum Discussion

gomezc73's avatar
gomezc73
Helper V
2 years ago
Solved

How Concatenate Text by Category

Hi Experts,   I have the following issue.   I have in a table the explanations of variations by accounts.   Something like these:   Lead Account Sep 23 Sep22 VARIATION Explanation ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    gomezc73 I posted the formula in the original reply. All you should have to do is replace 'Table' with your actual table name assuming that your columns names are what you posted.

     

gomezc73's avatar
gomezc73
Helper V
2 years ago

Hi, i can't open the PBI because i have a prior version installed, can you please send me an screenshot of the formula?.. i reaaly appreciatte

Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

gomezc73 I posted the formula in the original reply. All you should have to do is replace 'Table' with your actual table name assuming that your columns names are what you posted.

 

  • gomezc73's avatar
    gomezc73
    Helper V
    2 years ago

    Amazing!!. worked perfect!!.

    thank you!!  

  • gomezc73's avatar
    gomezc73
    Helper V
    2 years ago

    Hi Greg, i have a lithe issue maybe you can help me with a variation of the measure..

     

    The final user told me that he only explain variations greater of 6%, then i have some accounts with the explanation field in blanks..

     

    I tried to filter only explanations not blanks, but then the sum of amounts also filter it and it is incorrect..

     

    There is a way of modify this measure to only concatenate explanations Non blanks without filter the amounts?.

     

    By example( see the below dada) the account I02 haven't explanation. then i need concatenate only the explanation of the accounts I01, I03, I04, but the Sum of the variations must be 10.980(considerand the amount with no explanation).

     

    When i filter only explanations Non Blanks the amount of variations is 11.000.

     

    Can you please help me?

    LeadAccount23-Sep22-SepVARIATIONExplanation
    OTHERI01200010001000Funds in this account are used to pay the payroll and any other payroll related items pertaining to the employees under this company
    OTHERI021098011000-20 
    OTHERI031000010009000Increase mainly driven by timing on the collection
    OTHERI04200010001000Payments for medical claims and other benefic

     

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      2 years ago

      gomezc73 Sure, you can filter the blanks in the measure:

      Explanation Measure = 
    VAR __PathText = CONCATENATEX(FILTER('Table', [Explanation] <> BLANK()), [Explanation], "|")
    VAR __Table = 
        ADDCOLUMNS(
            GENERATESERIES(1, COUNTROWS('Table'), 1),
            "__Text", PATHITEM(__PathText, [Value])
        )
    VAR __Result = CONCATENATEX(__Table, [Value] & " " & [__Text] & UNICHAR(10) & UNICHAR(13))
RETURN
    __Result
      • gomezc73's avatar
        gomezc73
        Helper V
        2 years ago

        Hi,

         

         I tried but it still it show at the end blanks lines numbered.. See the image