Forum Discussion

bhalicki's avatar
bhalicki
Icon for Helper V rankHelper V
1 year ago
Solved

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

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: Pr...
Need Help
  • v-ssriganesh's avatar
    v-ssriganesh
    1 year ago

    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's avatar
bhalicki
Icon for Helper V rankHelper V
1 year ago

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's avatar
v-ssriganesh
Icon for Community Support rankCommunity Support
1 year ago

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's avatar
    bhalicki
    Icon for Helper V rankHelper V
    1 year ago

    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's avatar
      v-ssriganesh
      Icon for Community Support rankCommunity Support
      1 year ago

      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's avatar
        bhalicki
        Icon for Helper V rankHelper V
        1 year ago

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

        Kind regards,

        Ben.