topic Re: Query with row values in DAX Commands and Tips

Query with row values

vpastor — Tue, 09 Jul 2024 11:42:36 GMT

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

Re: Query with row values

vpastor — Tue, 09 Jul 2024 11:43:35 GMT

Ok, i have edited my message 😉

Re: Query with row values

Anonymous — Fri, 12 Jul 2024 08:38:56 GMT

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.