Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a report that uses DirectQuery to connect to an SQL Server DB. In the report, there is a measure that uses the DATEDIFF function. This measure will calculate correctly in Power BI Desktop. However, once I publish the report to the Power BI Service (now connecting via the On-Premesis Data Gateway connection), the measure returns blank/null. All other components appear to be working fine in that report, including other measures that don't use DATEDIFF.
The reason I suspect DATEDIFF is the culprit is because of its history. Apparently it used to not be supported with DirectQuery, but eventually that was changed. It is now considered a supported function: https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-direc...
So I suspect that when DATEDIFF and other functions were adpoted into DirectQuery's support model, the change wasn't fully implemented on Gateway connections. I do have the latest version of the Data Gateway. Does anyone have any other insight into this issue? Should I go ahead and open a ticket with Microsoft about this?
Solved! Go to Solution.
Hi @S_Sanchez,
AFAIK, current power bi service does not support local DateTime, it will convert local DateTime values to UTC format. (NOW function will get local DateTime when you use on power bi desktop side, but it may get the wrong result when you calculate on service side)
If this issue is related to NOW function, you can try to use UTCNOW function to instead:
Count_LastX = SUMX ( FILTER ( transaction_history, DATEDIFF ( transaction_history[Transaction_datetime], UTCNOW (), MINUTE ) <= Minutes[Minutes Value] ), [Total Meals] )
Regards,
Xiaoxin Sheng
HI @S_Sanchez ,
Can you please share your measure formula and some sample data with the same data structure for test? It is hard to troubleshoot without any detail sample data.
In addition, power bi service not has a limitation on use DATEDIFF function in measure formula. Did you double-check on that formula to confirm it can get corresponding parameters to calculate? AFAIK, it will return blank if you try to calculate between normal date value and blank value.
Sample measure= DATEDIFF ( BLANK (), TODAY (), DAY )
Regards,
Xiaoxin Sheng
Hi @S_Sanchez,
AFAIK, current power bi service does not support local DateTime, it will convert local DateTime values to UTC format. (NOW function will get local DateTime when you use on power bi desktop side, but it may get the wrong result when you calculate on service side)
If this issue is related to NOW function, you can try to use UTCNOW function to instead:
Count_LastX = SUMX ( FILTER ( transaction_history, DATEDIFF ( transaction_history[Transaction_datetime], UTCNOW (), MINUTE ) <= Minutes[Minutes Value] ), [Total Meals] )
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.