Create Table only with records that met the conditions

[kta87]

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

[jewel_at]

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

[AlB]

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.

[kta87]

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

[AlB]

@kta87 

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

[kta87]

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

[AlB]

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.

[kta87]

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.

[AlB]

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?

[kta87]

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.

[AlB]

@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.

[AlB · Accepted solution]

@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.

[kta87]

This is brilliant. Thank you