topic Re: Create Table only with records that met the conditions in DAX Commands and Tips

Create Table only with records that met the conditions

kta87 — Fri, 23 Dec 2022 18:27:46 GMT

Hi everyone,

I have a request that gives me a hard time. I have to create a table with data from 2 tables bringing only the records from Table A that by their sum Amount group are equal or less with the corespondent Amount from Table B. The relationship is Table A (Many) - (One) Table B by the Customer ID.

Here are the 2 tables:

The expected result table:

Thank you in advance,

Kta

Re: Create Table only with records that met the conditions

AlB — Fri, 23 Dec 2022 18:40:21 GMT

Hi kta87

This looks like something that should be done in Power Query rather than DAX. What is the relationship for??

Can you please paste tables A and B in text-tabular format here, instead of in screen caps, so that the contents can be copied?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: Create Table only with records that met the conditions

jewel_at — Fri, 23 Dec 2022 19:44:16 GMT

Hi kta87

You can try this:

Table C = FILTER(ADDCOLUMNS('Table A', "Compare", VAR CustomerID = 'Table A'[CustomerID] var SumOfTableA = CALCULATE(SUM('Table A'[Amount]), 'Table A'[CustomerID] = CustomerID) VAR SumOfTableB = CALCULATE(sum('Table B'[Amount]), 'Table B'[CustomerID] = CustomerID) VAR Test = IF (SumOfTableA <= SumOfTableB, TRUE(), FALSE()) return Test ), [Compare] = TRUE())

Hope this helps!

Jewel

Re: Create Table only with records that met the conditions

kta87 — Fri, 23 Dec 2022 18:59:13 GMT

Hi AIB,

Power Query is fine also, if it does the job.

Table A
Customer ID	Document Number	Amount
Customer A	9	12
Customer A	10	10
Customer A	12	10
Customer B	13	10
Customer B	15	15
Customer B	16	25
Customer B	35	10
Customer B	122	12
Customer C	145	100
Customer D	456	200

Table B
Customer ID	Document Number	Amount
Customer A	4	30
Customer B	45	80
Customer C	345	80
Customer D	1567	200

Re: Create Table only with records that met the conditions

AlB — Fri, 23 Dec 2022 19:02:27 GMT

kta87

To clarify, why is the third row for Customer A (doc number 12) discarded?

Re: Create Table only with records that met the conditions

kta87 — Fri, 23 Dec 2022 19:10:01 GMT

It's not discarded, it shoud be in the table.

Re: Create Table only with records that met the conditions

AlB — Fri, 23 Dec 2022 19:26:03 GMT

What do you mean? It is not in your output table TableC

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: Create Table only with records that met the conditions

AlB — Fri, 23 Dec 2022 19:27:27 GMT

kta87

Place the following M code in a blank query to see the steps.See it all at work in the attached file.

let Source = TableA, #"Merged Queries" = Table.NestedJoin(Source, {"Customer ID"}, TableB, {"Customer ID"}, "Table2", JoinKind.LeftOuter), #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Amount"}, {"Table2.Amount"}), #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Cumul", each List.Sum(Table.SelectRows(#"Expanded Table2", (inner)=> [Customer ID]=inner[Customer ID] and [Document Number]>=inner[Document Number])[Amount]), Int64.Type), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Cumul] <= [Table2.Amount])), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Table2.Amount", "Cumul"}) in #"Removed Columns"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: Create Table only with records that met the conditions

kta87 — Fri, 23 Dec 2022 19:33:39 GMT

Because only the records from Table A whose sum is less than or equal to the value from Table B must be in Table C. For the customer A, document 9 and 10 can be in table C or 10 and 12 also is fine.

Re: Create Table only with records that met the conditions

AlB — Fri, 23 Dec 2022 19:58:15 GMT

Then the TableC that you show is incorrect? It does NOT show Customer A, document number 12

What do you mean by "whose sum is less..." ? What sum is that?

Re: Create Table only with records that met the conditions

kta87 — Fri, 23 Dec 2022 21:02:42 GMT

So for each customer from Table B must be brought documents from Table A, but their total amount must not exceed the amount from the Table B.
Customer A has 30 amount in Table B, so the correct records can be either documents 9 & 10 (12+10 = 22 which is <= 30) or documents 9 & 12 (12+10 = 22 which is <= 30) from Table A. Both variants are correct, the logic must bring only one.

Re: Create Table only with records that met the conditions

AlB — Fri, 23 Dec 2022 21:09:39 GMT

kta87

The solution I posted above does exactly that. It keeps the first variant that neets the requirement.

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: Create Table only with records that met the conditions

kta87 — Fri, 23 Dec 2022 22:01:42 GMT

This is brilliant. Thank you