Sumifs in Dax

[EugenioBeltran]

Hello I am new to DAX and need some help

I manage hundreds of purchase orders and need a create a key or bridge that will kelp me join two tables together

In the first table i can have 1 or several summarized (by cases) purchase orders (PO) with the same number but with different amount of cases  so I created at the columns a concatenate([purchase],[cases]), as a key, all good so far

The second table has the same purchase order (PO) numbers but the cases are not summarized, they are broken down by item so the cases need to be summarized based on a column named sales order (So) so that the sum of cases will match the cases per purchase order of the first table and then do a concatenate like in table 1 to create the key to create a relationship between the two tables

Each table contains different data that is needed to create a report so the two need to be joined.

Table 1 example

PO cases concatenate

A.   20.     A20

A.   24     A25

B    12     B15

B.   24.    B24

Table 2 example 

PO     So       item   cases

A          1        a          8

A          1        b         12. Sum So 1= 20

A          2        a          4

A.         2.       b.         20 Sum So 2 = 24

B          3        a          4

B          3        b          8 Sum So 3 = 12

B          4        c          12

B          4        a          12 Sum So 4 = 24So in table two the sum of the So will allow me to concatenate the PO with the cases and create a key to create a relationship between the two tables

Hope it makes sense

[FreemanZ]

hi @EugenioBeltran 

tamerj1's solution worked perfectly. you may also try like:

column2 = 
[PO]&
SUMX(
    FILTER(
        Table2,
        Table2[PO]=EARLIER(Table2[PO])
            &&Table2[SO]=EARLIER(Table2[SO])
    ),
    Table2[Case]
)

they worked like:

[EugenioBeltran]

It worked, it worked but know it's saying We could not refresh the Data Model from the source data. Here is the error message we got. A circular discrepancy was detected "Table2(key), table2(key), Table2 (key), no idea were to look for it

[EugenioBeltran]

I also used your formula to calculate the pallets, no dure if this is causing it

SUMX( FILTER( Table2, Table2[PO]=EARLIER(Table2[PO]) &&Table2[SO]=EARLIER(Table2[SO]) ), Table2[Calculated pallets]) ?

Or the one I used before to calculate the pallets calculated pallets = table2(cases per pallet/table2cases)

[tamerj1]

Hi @EugenioBeltran 

Please try

concatenate =
Table2[PO]
& CALCULATE (
SUM ( Table2[cases] ),
ALLEXCEPT ( Table2, Table2[PO], Table2[SO] )
)

[EugenioBeltran]

It did not work for me maybe I did not enter it correctly, tried several times

[tamerj1]

@EugenioBeltran 
Would you please provide some screenshots for better understanding of the situation?

[EugenioBeltran]

Here are the picture hope they are clear

  

 

 

[tamerj1]

@EugenioBeltran 

Seems like most of the columns in Table2 are calculated columns based on Table1. It is really difficult to detect the source of the circular dependency error(s) without working on the file itself. 

[EugenioBeltran]

Can I share?

[tamerj1 · Accepted solution]

@EugenioBeltran 
Yes, you can use WeTransfer or any cloud service to share the download link

[EugenioBeltran]

https://docs.google.com/spreadsheets/d/1gnT4vTQaQ1Ez8MA3maJ27FDcsTr7MUYB/edit?usp=drivesdk&ouid=1086... 

[tamerj1]

@EugenioBeltran 
I can see that there is no data model and the power pivot is empty!

[EugenioBeltran]

Try this link pls 

https://docs.google.com/spreadsheets/d/1MWulNkfyjIZe2arO_wkMW5bf116EGfHp/edit?usp=drivesdk&ouid=1086....  

If this does not work, maybe send by e mail?

[EugenioBeltran]

@tamerj1   try this link please

https://docs.google.com/spreadsheets/d/1MWulNkfyjIZe2arO_wkMW5bf116EGfHp/edit?usp=drivesdk&ouid=1086...