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.
Hi
I have two tables case and caseorganisationsponsorid. I would like to be able to perform a LEFT JOIN so all comes back from the case table whether the caseorganisationsponsorid table has a value present for the join or not (hence the LEFT JOIN). To add, the right-sided table performs some grouping/concatenation.
Are joins in Power BI/DAX (not Power Query) purely INNER JOINS unless you specify a function like NATURALLEFTOUTERJOIN ?
I've tried a few things but am getting in a pickle. My latest attempt is below which doesn't work, but it should be enough for you to understand my aim. I'm aware this can be done in Power Query but to learn new ways I would like to understand how to achieve this using DAX?? In placing the measure in the table visual should (hopefully) follow row context filtering so the concatenation of organisations should be placed on the relevant caseid row in the table visual.
Please can someone help?
Thanks.
Solved! Go to Solution.
Hi @D_PBI ,
We can think about LEFT JOIN as a subset of CROSS JOIN result. In SQL such logic was used at start and only after we received separate syntax for LEFT and RIGHT JOINs. So based on this we can use a function GENERATEALL (similar to CROSS APPLY in SQL).
Here is an example of table showing join using DAX:
Table Example =
GENERATEALL(
'Table1',
VAR __Table1_Col1 = Table1[Col1]
RETURN
CALCULATETABLE('Table2',
Table2[Col5] = __Table1_Col1
)
)
Input tables:
Table1
Table2
Table result of an aforementioned formula
Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!
You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups
Hi @D_PBI ,
We can think about LEFT JOIN as a subset of CROSS JOIN result. In SQL such logic was used at start and only after we received separate syntax for LEFT and RIGHT JOINs. So based on this we can use a function GENERATEALL (similar to CROSS APPLY in SQL).
Here is an example of table showing join using DAX:
Table Example =
GENERATEALL(
'Table1',
VAR __Table1_Col1 = Table1[Col1]
RETURN
CALCULATETABLE('Table2',
Table2[Col5] = __Table1_Col1
)
)
Input tables:
Table1
Table2
Table result of an aforementioned formula
Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!
You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups
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 |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |