Forum Discussion

gomezc73's avatar
gomezc73
Icon for Helper V rankHelper 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.

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity 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
Icon for Helper V rankHelper 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
    Icon for Community Champion rankCommunity 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
      Icon for Helper V rankHelper V
      2 years ago

      Hi,

       

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