Outer Join in Direct Query

Hi

Thanks for your prompt reply. Setting the cross filter direction did not help though. I cannot figure out why PBI decides to create an inner join on this relationship:

The output I get on a simple test table:

This is the full query being generated to update this table:

// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"KPI00005"}, 'DQ_KPI_BASE'[NK_KPI_CD])

VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL('DI_Management'[Level1_KorteNaam], "IsGrandTotalRowTotal"),
__DS0FilterTable,
"SumGood", CALCULATE(SUM('DQ_KPI_BASE'[Good]))
)

VAR __DS0PrimaryWindowed =
TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'DI_Management'[Level1_KorteNaam], 1)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
[IsGrandTotalRowTotal] DESC, 'DI_Management'[Level1_KorteNaam]

// Direct Query

SELECT
TOP (1000001) [t1].[Level1_KorteNaam],[t1].[NK_CodeOrganisatieeenheid]
FROM
(
(
SELECT distinct [TK_Organisatieeenheid]
,[NK_CodeOrganisatieeenheid] COLLATE Latin1_General_CI_AS as [NK_CodeOrganisatieeenheid]
,[NK_ID_Organisatieeenheid]
,[Omschrijving]
,[Level4_Naam]
,[Level3_Naam]
,[Level2_Naam]
,[Level1_Naam]
,[Level4_KorteNaam]
,[Level3_KorteNaam]
,[Level2_KorteNaam]
,[Level1_KorteNaam]
FROM [EDM].[ORG].[vw_DI_HROrganisatiestructuur] hr
-- inner join DQ_Framework.draft.vw_dq_kpi_base b on hr.NK_CodeOrganisatieeenheid=b.ACC_ORG_EENHEID_CODE COLLATE Latin1_General_CI_AS
where TF_Deleted=0 and tf_actif=1
and getdate() between HD_ValidFrom and HD_ValidTo
)
)
AS [t1]
GROUP BY [t1].[Level1_KorteNaam],[t1].[NK_CodeOrganisatieeenheid]

// Direct Query

SELECT
TOP (1000001) *
FROM
(

SELECT [semijoin1].[c17],SUM(
CAST([a0] as BIGINT)
)
AS [a0]
FROM
(
(

SELECT [t2].[ACC_ORG_EENHEID_CODE] AS [c38],[t2].[Good] AS [a0]
FROM
(
(
select [_].[TK_KEY] as [TK_KEY],
[_].[TF_CreateDate] as [TF_CreateDate],
[_].[TF_JobId] as [TF_JobId],
[_].[TF_Source] as [TF_Source],
[_].[TF_SourceFileName] as [TF_SourceFileName],
[_].[TF_Partition] as [TF_Partition],
[_].[NK_KPI_CD] as [NK_KPI_CD],
[_].[KPI_DATE] as [KPI_DATE],
[_].[KPI_DETAIL_KEY] as [KPI_DETAIL_KEY],
[_].[KPI_VALUE] as [Good],
[_].[ACC_BOE] as [ACC_BOE],
[_].[ACC_ORG_UNIT_ID] as [ACC_ORG_UNIT_ID],
[_].[ACC_ORG_EENHEID_CODE] as [ACC_ORG_EENHEID_CODE],
[_].[Kantoorcode] as [Kantoorcode]
from [Draft].[VW_DQ_KPI_BASE] as [_]
)
)
AS [t2]
WHERE
(
[t2].[NK_KPI_CD] = 'KPI00005'
)

)
AS [basetable0]

INNER JOIN

(

(SELECT 3 AS [c17],'0000000000' AS [c38] ) UNION ALL
(SELECT 4 AS [c17],'0000000001' AS [c38] ) UNION ALL
...

(SELECT 10 AS [c17],'9030204000' AS [c38] ) UNION ALL
(SELECT 10 AS [c17],'9030205000' AS [c38] ) UNION ALL
(SELECT 10 AS [c17],'9030206000' AS [c38] ) UNION ALL
(SELECT 6 AS [c17],'9040000000' AS [c38] ) UNION ALL
(SELECT 6 AS [c17],'9040100000' AS [c38] ) UNION ALL
(SELECT 4 AS [c17],'9040200000' AS [c38] ) UNION ALL
(SELECT 6 AS [c17],'9040201000' AS [c38] ) UNION ALL
(SELECT 6 AS [c17],'9040202000' AS [c38] ) UNION ALL
(SELECT 11 AS [c17],'9800000000' AS [c38] ) UNION ALL
(SELECT 3 AS [c17],'9900000000' AS [c38] ) UNION ALL
(SELECT 4 AS [c17],'Not Applicable' AS [c38] ) UNION ALL
(SELECT 4 AS [c17],'Not Found' AS [c38] )
)
AS [semijoin1] on
(

([semijoin1].[c38] = [basetable0].[c38])

)
)

GROUP BY [semijoin1].[c17]
)
AS [MainTable]
WHERE
(

NOT(
(
[a0] IS NULL
)
)

)

// Direct Query

SELECT SUM(
CAST([t2].[Good] as BIGINT)
)
AS [a0]
FROM
(
(
select [_].[TK_KEY] as [TK_KEY],
[_].[TF_CreateDate] as [TF_CreateDate],
[_].[TF_JobId] as [TF_JobId],
[_].[TF_Source] as [TF_Source],
[_].[TF_SourceFileName] as [TF_SourceFileName],
[_].[TF_Partition] as [TF_Partition],
[_].[NK_KPI_CD] as [NK_KPI_CD],
[_].[KPI_DATE] as [KPI_DATE],
[_].[KPI_DETAIL_KEY] as [KPI_DETAIL_KEY],
[_].[KPI_VALUE] as [Good],
[_].[ACC_BOE] as [ACC_BOE],
[_].[ACC_ORG_UNIT_ID] as [ACC_ORG_UNIT_ID],
[_].[ACC_ORG_EENHEID_CODE] as [ACC_ORG_EENHEID_CODE],
[_].[Kantoorcode] as [Kantoorcode]
from [Draft].[VW_DQ_KPI_BASE] as [_]
)
)
AS [t2]
WHERE
(
[t2].[NK_KPI_CD] = 'KPI00005'
)