Dataset in report has null in colunm, but data exists when executing query in SSMS or DaxStudio

[jacccodezwart]

Hi all,

Strange thing here.

I have a dax query which works fine in SSMS and in Dax Studio when I connect to SSRS-Server instance.

But the same query shows for one column all null values in Report Builder when testing my report.

What can it be ?

Here is the query :

EVALUATE
SUMMARIZECOLUMNS (
    'Datum'[Maand],
    'StockTerminal'[LocationName],
    'YearChart'[GroupedCommodity],
    'Datum'[Datum],
    'Datum'[JaarMaand],
    'Datum'[Jaar],
    FILTER (
        VALUES ( 'YearChart'[GroupedCommodity] ),
        ( 'YearChart'[GroupedCommodity] <> BLANK () )
    ),
    KEEPFILTERS ( TREATAS ( { "ARRIVED" }, YearChart[InboundStatus] ) ),
    FILTER ( VALUES ( 'Datum'[Jaar] ), ( 'Datum'[Jaar] = YEAR ( TODAY () ) ) ),
    "Qty", [Qty],
    "QtyPreviousMonth", [QtyPreviousMonth],
    "QtySamePeriodeLY", [QtySamePeriodeLY],
    "QtySamePeriode2YAGo", [QtySamePeriode2YAgo],
    "Difference", ( [Qty] - [QtySamePeriodeLY] ),
    "Difference2423", ( [QtySamePeriodeLY] - [QtySamePeriode2YAgo] )
)

[Poojara_D12]

Hi @jacccodezwart 

You're experiencing a puzzling issue where your DAX query works perfectly in SSMS and DAX Studio, returning correct values for all columns, but when used in Report Builder (for Paginated Reports) against the same SSRS server, one of the measure columns (likely [Qty] or its derivatives) returns only NULLs. This kind of discrepancy usually stems from differences in how evaluation context, security roles, or row-level security (RLS) are applied in Report Builder versus tools like DAX Studio or SSMS.

In Report Builder, the execution context is affected by the user identity under which the report runs. If row-level security (RLS) is defined on the semantic model and you're testing in Report Builder as a user who lacks access to some portion of the data, the result might show as NULL—even though the same query works under a different identity in SSMS or DAX Studio, which often use elevated or unrestricted access. This behavior is especially common with calculated measures that rely on filtered row contexts.

Another possibility is that Report Builder, when connecting via XMLA or the Analysis Services endpoint, handles certain query optimizations differently or applies default values or culture settings that can influence measure evaluation, especially if any implicit conversion or locale-sensitive logic (like date parsing) is involved in the underlying measures.

To troubleshoot:

Test the report in Report Builder with your own credentials and ensure you're not restricted by RLS. Also try using "Run as admin" if available.

Double-check the definition of the affected measure (e.g., [Qty]) in your model. It may contain filters or expressions that resolve differently depending on the execution context.

Use Performance Analyzer in Power BI Desktop (if the same model is used) or log queries from SSAS Profiler to compare execution traces between tools.

Ensure that the Report Builder data source is pointing to the correct workspace or semantic model, especially if multiple versions exist.

[jacccodezwart]

Hi Poojara,

Thanks for you elaborate explanation of what goes on and where it possible might go 'wrong'. 

I think it is worth to investicate and try you route to troubleshoot my issue.

I'll let you know how things went.

Jacco

[rohit1991]

Hi @jacccodezwart ,
Based on what you've described, it sounds like the issue you're facing is likely tied to how Report Builder (SSRS) processes DAX queries differently from tools like SSMS and DAX Studio. Even though the query returns correct values elsewhere, SSRS may be applying implicit filters or handling context in a slightly different way during report execution.

Since removing BLANK() and date filters didn't help, and you’re only seeing nulls for a specific measure like QtySamePeriode2YAGo, it could be that the way the dataset or its relationships are interpreted within Report Builder is affecting that specific calculation. Sometimes, a workaround like splitting the logic into separate queries—as you already tried—can bypass the issue, even if it’s not ideal.

That said, since SSRS and Power BI often behave differently under the hood despite sharing back-end models, raising a support ticket with Microsoft (when possible) might still be your best shot at identifying if it’s a limitation or a bug. In the meantime, your workaround seems like a practical solution, especially if it gives you the expected result reliably.

[v-karpurapud]

Hi @jacccodezwart 

Could you please confirm if you have raised the support ticket and if the issue has been resolved after raising a support ticket? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.

Thank you !

[jacccodezwart]

Sorry, I haven't been able to raise a support ticket. I have made a work-a-round by reqeusting the data with an additional query rather all in one

[v-karpurapud]

Hi @jacccodezwart 

We are following up once again regarding your query. If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation.

[jacccodezwart]

Hi @v-karpurapud , 

Like I mentioned earlier, the work-a-round was to create a separate query. 

I was not able to create a ticket at Microsoft support due to some information that I missed when I tried to create the ticked.

[v-karpurapud]

Hi @jacccodezwart 

Thank you for the update.

Since you were unable to create a support ticket previously due to missing information, we kindly recommend trying again with the correct and complete details. This will help ensure the request is successfully submitted and routed to the appropriate support team at Microsoft.


Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.

[v-karpurapud]

Hi @jacccodezwart 

Could you please confirm if the issue has been resolved through the support ticket with Microsoft?If the issue has been resolved, we kindly request you to share the resolution or key insights here to assist others in the community. If we do not hear back, we will proceed to close this thread.For any further discussions or questions, please start a new thread in the Microsoft Fabric Community Forum  we’ll be happy to assist. We will be happy to help.

Thank you .

[v-karpurapud]

Hi @jacccodezwart 

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and give a kudos, as this helps other members in  community.

Thank You!

[v-karpurapud]

Hi @jacccodezwart

Thank you for reaching out to the Microsoft Community Forum.
 

The issue where the DAX query works fine in SSMS and DAX Studio but returns NULL for a column in Report Builder indicates a problem within the Report Builder environment affecting data retrieval.

If GroupedCommodity contains BLANK() values, those rows are excluded, which could impact the results in Report Builder but not necessarily in SSMS/DAX Studio (due to caching or context differences). Modify the filter to handle BLANK().

The DAX query might behave differently in Report Builder due to implicit filters. Check Report Builder’s dataset properties to ensure that additional filters are not applied.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you.

[jacccodezwart]

Hi @v-karpurapud ,

I tried you suggestion to  look at the filtering. I have removed the BLANK() filtering, even removed the date filter, that didn't result in values for the QtySamePeriode2YAGo.

Perhaps the the retrieval of the data trough SSRS has some influence. But bot SSMS and Dax Studio retrieve that same data trough SSRS.

[v-karpurapud]

Hi @jacccodezwart 

Thank you for testing the suggestions and providing your feedback. Since the issue persists even after removing BLANK() filtering and date filters, and considering that both SSMS and DAX Studio return correct values while SSRS does not, it seems to be related to how SSRS retrieves and processes the data.

Given the complexity of SSRS data retrieval and connection handling, I recommend raising a Microsoft Support ticket to investigate further. The support team can help analyze SSRS execution logs, connection settings, and any potential limitations affecting data retrieval

You can submit a ticket through the Microsoft Power BI Support Portal:

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.

Thank you!