Forum Discussion
Helper VShared semantic model vs local model - different results for same measure code
Hi bhalicki,
We regret the inconvenience caused and acknowledge your requirements. Please consider raising a Microsoft support ticket for further investigation. You can explain all the troubleshooting steps you have taken to help them better understand the issue.
You can create a Microsoft support ticket with the help of the link below:
https://learn.microsoft.com/en-us/power-bi/support/create-support-ticketIf this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
Community SupportHi bhalicki,
Thank you for posting your query and providing detailed information. The difference in results between using a local model (DimCalendar) and a shared semantic model (DimCalendarRemote) with the same measure could be due to several factors:
- The relationship with DimCalendarRemote might handle filtering differently due to its shared semantic model nature, potentially affecting context propagation in Direct Query mode.
- DimCalendarRemote, if not fully refreshed or synchronized, might yield different results compared to the local DimCalendar or If DimCalendarRemote uses Direct Query while the local model is imported, differences in query execution or source database behavior could impact the MAX(DateKey) calculation.
- The SELECTEDVALUE(ProjectTargets[ProjectKey]) might behave differently based on the active relationship and context in the shared model.
Here are the few troubleshooting steps you can consider:
- Verify that the relationships between ProjectTargets and both DimCalendar tables are correctly set up and active.
- Test the measure with a simple MAX(ProjectTargets[DateKey]) to isolate the issue.
- Check the Power BI Service for any refresh delays or performance issues with DimCalendarRemote.
- Check the data types and values in DateKey and ProjectKey are consistent across both models.
If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
Helper VHi v-ssriganesh ,
Thank you for your reply, much appreciated.
I have checked as per your suggestions and cannot find anything that would cause this issue. Data types are consistent. Using a simple measure MAX(ProjectTargets[DateKey]) yields the same results for both local and remote DimCalendar tables, but this is expected as it doesn't filter the row context by the Project Key.
Is there anything else I can try, or does this need to be escalated to Microsoft as a bug? Seems very strange behaviour.
Kind regards,
Ben.
- v-ssriganesh
Hi bhalicki,
Thank you for getting back and providing the additional details. It’s great that you’ve confirmed the data types are consistent and that a simple MAX(ProjectTargets[DateKey]) works as expected across both tables. Since the issue persists with the LatestProjectMonth measure (which includes filtering by ProjectKey), it does suggest a potential context or relationship evaluation difference between the local and remote models.
- Ensure the filter direction and cross-filtering settings between ProjectTargets and DimCalendarRemote are identical to those with DimCalendar. A mismatch here could affect the row context.
- Modify the measure to use ALLSELECTED(ProjectTargets) instead of ALL(ProjectTargets) to see if the selected context changes the outcome.
- Use Power BI’s Performance Analyzer to compare the query steps for both scenarios. This might reveal differences in how the shared semantic model processes the filter.
I trust this addresses your needs. If it does, please “Accept as solution” and give it a "kudos" to help others find it easily.
Thank you.- bhalicki
Hi v-ssriganesh ,
Thank you once again for your help.
I have confirmed cross-filtering is correct.
Here are the queries:Working table (using local semantic model)
// DAX Query
DEFINE
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP('DimCalendarLocal'[MonthLong], 'ProjectUpdate'[Target], 'ProjectUpdate'[Project]), "IsGrandTotalRowTotal"
),
"LastUpdate", 'ProjectUpdate'[LastUpdate],
"SimpleMeasure", 'ProjectUpdate'[SimpleMeasure]
)VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0Core,
[IsGrandTotalRowTotal],
0,
'ProjectUpdate'[Project],
1,
'DimCalendarLocal'[MonthLong],
1,
'ProjectUpdate'[Target],
1
)EVALUATE
__DS0PrimaryWindowedORDER BY
[IsGrandTotalRowTotal] DESC,
'ProjectUpdate'[Project],
'DimCalendarLocal'[MonthLong],
'ProjectUpdate'[Target]Incorrect query (remote semantic model):
// DAX Query
DEFINE
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP('ProjectUpdate'[Target], 'ProjectUpdate'[Project], 'DimCalendarRemote'[MonthLong]), "IsGrandTotalRowTotal"
),
"LastUpdate", 'ProjectUpdate'[LastUpdate],
"SimpleMeasure", 'ProjectUpdate'[SimpleMeasure]
)VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0Core,
[IsGrandTotalRowTotal],
0,
'ProjectUpdate'[Project],
1,
'ProjectUpdate'[Target],
1,
'DimCalendarRemote'[MonthLong],
1
)EVALUATE
__DS0PrimaryWindowedORDER BY
[IsGrandTotalRowTotal] DESC,
'ProjectUpdate'[Project],
'ProjectUpdate'[Target],
'DimCalendarRemote'[MonthLong]
// Direct Query
EVALUATE
SUMMARIZE(
VALUES('DimCalendarRemote'),
'DimCalendarRemote'[MonthLong]
)
// Direct Query
DEFINE
VAR _Var0 = VALUES('DimCalendarRemote'[MonthLong])
EVALUATE
GROUPCROSSAPPLYTABLE(
'DimCalendarRemote'[MonthLong],
NATURALJOINUSAGE(_Var0),
"L1",
SUMMARIZE(
VALUES('DimCalendarRemote'),
'DimCalendarRemote'[Date]
)
)There is indeed a difference, but I am not sure why, or why it should impact how the measure is evaluated.
Kind regards,Ben.
- v-ssriganesh
Hi bhalicki,
Thank you for providing the detailed DAX query plans this is helpful for narrowing down the issue.
- Modify the remote model query to match the ROLLUPGROUP order of the local model (i.e., ROLLUPGROUP('DimCalendarRemote'[MonthLong], 'ProjectUpdate'[Target], 'ProjectUpdate'[Project])) and test if this resolves the issue.
- If feasible, try importing the DimCalendarRemote table into the model instead of using Direct Query. This can help determine if the Direct Query mode is the root cause.
- Temporarily simplify the LatestProjectMonth measure to avoid SELECTEDVALUE and test with a hardcoded ProjectKey value to see if the issue is related to context propagation.
I trust this information proves useful. If it does, kindly “Accept as solution” and give it a "kudos" to help others locate it easily.
Thank you. - Modify the remote model query to match the ROLLUPGROUP order of the local model (i.e., ROLLUPGROUP('DimCalendarRemote'[MonthLong], 'ProjectUpdate'[Target], 'ProjectUpdate'[Project])) and test if this resolves the issue.