Same DAX Query, works with MS SQL Server as source but not PostgreSQL as source (direct query mode)?

[quangbilly79]

We just migrated from MS SQL Server to Postgre, some visuals in PowerBI work fine after changing Data Sources, but some just bugged out.

For example the same DAX Query below

New_e_now = VAR x = MAX(FactEmployeeContractSnapshotCDP[Fulldate])
var y =
CALCULATE(COUNTROWS(FactEmployeeContractSnapshotCDP),

USERELATIONSHIP(DimDate[DateKey],FactEmployeeContractSnapshotCDP[JoinningDateKey]),

FILTER(FactEmployeeContractSnapshotCDP,
YEAR(FactEmployeeContractSnapshotCDP[EmployeeJoiningDate])=YEAR(x)
&&
MONTH(FactEmployeeContractSnapshotCDP[EmployeeJoiningDate])=MONTH(x)
)
)
return y

Works fine when I use MS SQL Server as Data Source.

But when I changed the  Data Source to Postgre I got the below error

Query (1, 1) Function 'DATE' is not allowed as part of calculated column DAX expressions on DirectQuery models.
After some testing, I found out the problem lies in one of these 2 parts (remove either of them works)

USERELATIONSHIP(DimDate[DateKey],FactEmployeeContractSnapshotCDP[JoinningDateKey]),

and
FILTER(FactEmployeeContractSnapshotCDP,
YEAR(FactEmployeeContractSnapshotCDP[EmployeeJoiningDate])=YEAR(x)
&&
MONTH(FactEmployeeContractSnapshotCDP[EmployeeJoiningDate])=MONTH(x)
)

What is going on? There aren't any Date functions here. Also why some simple functions like YEAR and MONTH functions don't even work?

I have done some research on the internet, and people said that this problem is usually because PowerBI DAX doesn't support some kind of functions. BUT IT WORKS WITH MS SQL SERVER AS SOURCE! So why? Is this some kind of SQL-translated problem?
I tried other sources like ClickHouse, and Dremio and got the same error, what the h? Does PowerBI only work with MS SQL Server, or does it work only with MS SQL Server?

[Uzi2019 · Accepted solution]

Hi @quangbilly79 

Power BI does work with other database. just a limitation of Direct query mode.

you can check the blog here for understanding.

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about

I hope I anwered your question!