Query with row values

[vpastor]

Hello everyone

I am desperate and do not know how I can realize the following:

I have two tables:

G_L Entry (1 table)

GL Account No	Amount
170000	5000 €
210000	3500 €
520000	1000 €
520222	2000 €
174000	5000 €
520000	8000 €
550000	1000 €

Acc_ Schedule Line (2 table)

Totaling

170|520|174|5200|5500

The totaling field is a text string composed of several values, for example (170|520|174|524|5200|5500).

I need to make a formula that is able to read those values and add the "Amount" when the "GL Account No" starts with one of the values we have read from the "Totaling" column.

For example this measure return this:

22.000 €

Thank you very much to all of you.

Regards
Vicente

[Anonymous · Accepted solution]

Hi @vpastor ,

1. In the power query editor, separate the TOTAL values.

2. Hold down the CTRL key and select all columns, inverse perspective columns.

3.Create measure.

Measure2 = 
VAR _vtable =
    FILTER (
        CROSSJOIN ( 'Table', 'Table2' ),
        FIND (
            FIXED ( 'Table2'[Value], 0, 2 ),
            FIXED ( 'Table'[GL Account No], 0, 2 ),
            ,
            BLANK ()
        )
            <> BLANK ()
    )
RETURN
    SUMX ( SUMMARIZE ( _vtable, [Amount], [GL Account No] ), [Amount] )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vpastor]

Ok, i have edited my message 😉