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 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
Solved! Go to Solution.
Hi @srlabhe123,
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi
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'
)
Hi @srlabhe123,
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |