Shared semantic model vs local model - different results for same measure code

[bhalicki]

Hi all,

Hoping someone can please explain some very odd behaviour which seems to depend on whether or not a table is created in a local model, or by direct query.

I have the following tables:
ProjectTargets:

DimCalendar (truncated table shown) (this is created locally):

DimCalendar =

VAR CalendarTable = ADDCOLUMNS (CALENDAR(DATE(2025,1,1), DATE(2025,12,31)),

                                "MonthLong", FORMAT ([date], "mmmm" ))

RETURN CalendarTable

DimCalendarRemote:

This table is identical to DimCalendar, but created in a separate semantic model and published to PowerBi Service.

Measure:
I have a measure created which calculates the latest project update (based on MAX(ProjectTargets[DateKey])).

LatestProjectMonth =

VAR CurrentProject = SELECTEDVALUE(ProjectTargets[ProjectKey])

RETURN CALCULATE(MAX(ProjectTargets[DateKey]), ALL(ProjectTargets), ProjectTargets[ProjectKey]=CurrentProject)

If I use the local copy of DimCalendar, with relationships setup as follows, this is what I get:

Note that the results above produce the expected output.

If I switch the relationship to use DimCalendarRemote, using the same measure, this is what I get:

Note that the results above differ based on where the DimCalendar is stored.

Hoping someone can please explain why the exact same measure produces different results when using a shared semantic model.

Kind regards,

Ben.

[v-ssriganesh]

Hi @bhalicki,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @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.

[bhalicki]

Hi @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
__DS0PrimaryWindowed

ORDER 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
__DS0PrimaryWindowed

ORDER 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.

[bhalicki]

Hi @v-ssriganesh ,

Thank you for getting back to me with the additional steps to try, much appreciated.

1 - Modifying the remote query to match the local query had no effect (using the remote DimCalendar still produced a different and incorrect result to the local DimCalendar model).

2 - Importing DimCalendarRemote into the local data model resolves the issue (however, this is not feasible as the issue I am replicating is based on how our production reporting environment is setup).  The issue appears to be triggered by the direct query DAX:
// 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]
    )
)

3 - Modifying LatestProjectMonth to hard-code the ProjectKey still produces incorrect results:

LastUpdate =

// VAR ProjectKey = SELECTEDVALUE(ProjectUpdate[Project])

VAR ProjectKey = 2

RETURN CALCULATE(MAX(ProjectUpdate[DateKey]), ALLSELECTED(ProjectUpdate), ProjectUpdate[Project]=ProjectKey)

I am not sure what else to try. It really feels like a bug.

Kind regards,

Ben.

[v-ssriganesh · Accepted solution]

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-ticket

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.

[bhalicki]

Hi @v-ssriganesh , I tried to raise to Microsoft support, but they just want me to raise to their partners (which is a paid service).  They think this is a problem with the DAX, but I cannot understand why the same query should execute differently based on the data location - it really feels like a bug to me.  Do you know of a way to escalate this internally to the Microsoft product team?

[bhalicki]

Thanks for your help @v-ssriganesh , I have raised a ticket with Microsoft support.  Much appreciated.

Kind regards,

Ben.