Forum Discussion
DAX - Select as RIGHT OUTER JOIN
Morning all!
I would like to solve a problem with DAX. My requirements are as follows:
- I have a TABLE A with Sku, Store, FamilySku (store stocks): sk1 - store1 - fam1 ; sk2 - store1 - fam1
- I have a TABLE B with Sku, FamilySku, Rank (warehouse stock): sk1 - fam1 - 1; sku51 - fam1 - 2;
For each row in table A I would like to create two new columns:
- First Sku of the same family in Table B that is not in table A
- Second Sku of the same family in Table B that is not in table A
In SQL it would be something like this:
select Sku from
tableA a Right outer join TableB b
On...
Where A.store = "X" and B.Rank = 1
Any ideas to start with?
Thank in you in advance!
Regards
You're essentially trying to perform a right outer join in DAX, which isn't as straightforward as SQL, but it's doable.
To achieve this, you can use the RELATEDTABLE and EXCEPT functions in DAX. Here's a rough idea of how you can approach this:
First, let's create a new table that filters TABLE B based on the FamilySku from TABLE A and excludes SKUs that are already in TABLE A:
FilteredTableB =
VAR CurrentFamilySku = SELECTEDVALUE('TABLE A'[FamilySku])
RETURN
FILTER(
EXCEPT(
FILTER('TABLE B', 'TABLE B'[FamilySku] = CurrentFamilySku),
FILTER('TABLE A', 'TABLE A'[FamilySku] = CurrentFamilySku)
),
'TABLE B'[Rank] <= 2
)
Now, let's create the two new columns in TABLE A:For the first SKU:
FirstSKU =
VAR FilteredRows = FilteredTableB
RETURN
MINX(FILTER(FilteredRows, 'TABLE B'[Rank] = 1), 'TABLE B'[Sku])
For the second SKU:SecondSKU =
VAR FilteredRows = FilteredTableB
RETURN
MINX(FILTER(FilteredRows, 'TABLE B'[Rank] = 2), 'TABLE B'[Sku])
These columns will give you the first and second SKUs from TABLE B that are not in TABLE A for the same FamilySku.
1 Reply
You're essentially trying to perform a right outer join in DAX, which isn't as straightforward as SQL, but it's doable.
To achieve this, you can use the RELATEDTABLE and EXCEPT functions in DAX. Here's a rough idea of how you can approach this:
First, let's create a new table that filters TABLE B based on the FamilySku from TABLE A and excludes SKUs that are already in TABLE A:
FilteredTableB =
VAR CurrentFamilySku = SELECTEDVALUE('TABLE A'[FamilySku])
RETURN
FILTER(
EXCEPT(
FILTER('TABLE B', 'TABLE B'[FamilySku] = CurrentFamilySku),
FILTER('TABLE A', 'TABLE A'[FamilySku] = CurrentFamilySku)
),
'TABLE B'[Rank] <= 2
)
Now, let's create the two new columns in TABLE A:For the first SKU:
FirstSKU =
VAR FilteredRows = FilteredTableB
RETURN
MINX(FILTER(FilteredRows, 'TABLE B'[Rank] = 1), 'TABLE B'[Sku])
For the second SKU:SecondSKU =
VAR FilteredRows = FilteredTableB
RETURN
MINX(FILTER(FilteredRows, 'TABLE B'[Rank] = 2), 'TABLE B'[Sku])
These columns will give you the first and second SKUs from TABLE B that are not in TABLE A for the same FamilySku.