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 ,

Usually, if your query returns all the right data in SSMS/DAX Studio but you see NULLs in Report Builder, it’s almost always related to the security context or some kind of hidden filtering going on inside the report environment.

Here’s what I always check:

1. Row-Level Security (RLS): Make sure you’re running the report as yourself, and check if any RLS roles could be filtering out rows behind the scenes. Sometimes SSRS uses a different execution account than your SQL/DAX Studio sessions.
   
   

2. Dataset Properties & Filters: Double-check the dataset properties in Report Builder for any filters, parameters, or even sorting that might remove rows with BLANK or NULL values. Also, check for any report-level filters.
   
   

3. BLANK/NULL Handling in DAX: Sometimes, calculated columns or measures can return BLANK in certain contexts (like in Report Builder) but not in others. Try wrapping your field in IF(ISBLANK(...), "No Data", ...) just for testing to see if you get different results.
   
   

4. Connection Differences: Confirm your connection string is exactly the same as what you use in SSMS/DAX Studio, sometimes subtle differences can matter (like integrated security vs. a hardcoded account).
   
   

5. Cache or Permissions Glitches: Occasionally, SSRS caches get sticky. Try redeploying the report, clearing the cache, or even creating a new dataset as a test.
   
   

If you’ve ruled out all of the above and still have the issue, it’s probably worth raising a ticket with Microsoft support (especially if this is a new behavior or only happens on your server). Sometimes, the underlying execution context or the way SSRS processes semantic models is just different enough to cause these hiccups.

If you have access to SQL Profiler or Performance Analyzer in Power BI Desktop, you can capture the actual query being sent by Report Builder and compare it to what you’re running in SSMS, occasionally you’ll spot an extra filter or parameter that’s sneaking in.

[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!