Forum Discussion
Outer Join in Direct Query
Hi Friends
I am trying to come up with final output as in fig below. Please note that Table 2 count is a measure created in Table 2. I wana have outer join setup so that always Final output should display all Routes form Table 1 as shown below
I could do this in Import query by creating columns and variables but as Directquery DAX does not support calculate hence I am facing issue
Hi Anonymous,
You need to enable the Measure for the direct query and in that way you can make them work without making the calculations on your query but on the desktop part:
Regards,
MFelix
4 Replies
Hi Anonymous,
You need to enable the Measure for the direct query and in that way you can make them work without making the calculations on your query but on the desktop part:
Regards,
MFelix
I have a similar problem. I have a fact table which joins to a dimension table in direct query mode. It looks like the query being generated always creates an inner join, but I want it to be an outer join. The option related to above seems to have disappeared?
Hi jvandyck ,
With the introduction of the composite models, this "unrestricted measures" is implicitly on. Since there is nothing to choose, the checkbox from the options dialog was taken out.
Try to work with the cross filter direction to see if you can accomnplishg:
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand
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
__DS0PrimaryWindowedORDER BY
[IsGrandTotalRowTotal] DESC, 'DI_Management'[Level1_KorteNaam]
// Direct QuerySELECT
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 QuerySELECT
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 QuerySELECT 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'
)
