DAX - Select as RIGHT OUTER JOIN

[sashaxiv]

Morning all!

I would like to solve a problem with DAX. My requirements are as follows:

1. I have a TABLE A with Sku, Store, FamilySku  (store stocks): sk1 - store1 - fam1 ; sk2 - store1 - fam1
2. 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:

1. First Sku of the same family in Table B that is not in table A  
2. 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

[technolog · Accepted solution]

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.