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] )
)

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

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