SUMMARIZE 2 tables with individual filters and remove duplicates

[joshua1990]

Hi experts!

I would like to create a table with dax based on 2 different tables.

The first table has this structure:

Saler Order	Date	Step	Value	Key
1	01.01.2022	60	5	TRUE
2	01.01.2022	50	5	TRUE
3	01.01.2022	99	5	FALSE

The second table has this structure

Saler Order	Date	Key	Value
1	01.01.2022	B	5
1	01.01.2022	A	5
2	01.01.2022	C	6
3	01.01.2022	C	6

Now I would like to get a table with unique Sales Orders based on both tables with the following filters:

• First table
  • All Orders with Step 60 or 99 and Key = FALSE
• Second table
  • All Salers Orders with Key = C

How would you do this in DAX?

[tamerj1]

Hi @joshua1990 

please try

Table3 =
VAR T1 =
SELECTCOLUMNS (
FILTER ( Table1, Table1[Step] IN { 60, 99 } && Table1[Key] = "FALAE" ),
"Saler Order", [Saler Order],
"Date", [Date],
"Value", [Value]
)
VAR T2 =
SELECTCOLUMNS (
FILTER ( Table2, Table2[Key] = "C" ),
"Saler Order", [Saler Order],
"Date", [Date],
"Value", [Value]
)
RETURN
GROUPBY (
UNION ( T1, T2 ),
[Saler Order],
[Saler Order],
"Value", SUMX ( CURRENTGROUP (), [Value] )
)