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:
How would you do this in DAX?
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] )
)