DAX - Select as RIGHT OUTER JOIN

sashaxiv — Thu, 02 Apr 2020 15:59:35 GMT

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

Re: DAX - Select as RIGHT OUTER JOIN

technolog — Sun, 03 Sep 2023 17:22:24 GMT

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.

topic DAX - Select as RIGHT OUTER JOIN in DAX Commands and Tips

DAX - Select as RIGHT OUTER JOIN

Re: DAX - Select as RIGHT OUTER JOIN