Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Morning all!
I would like to solve a problem with DAX. My requirements are as follows:
For each row in table A I would like to create two new columns:
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
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |