Forum Discussion
translating SQL to DAX in DirectQuery Mode
What does the SQL query below translate to in DAX? SELECT
COUNT(dbo.tblUserUpload.Id) AS theCount
FROM
dbo.tblLoanStatus INNER JOIN
dbo.tblLoanStatus AS tblLoanStatus_CM ON dbo.tblLoanStatus.CM_...
Why are you trying to do this? It would be easier for us to help you if we understand the context better.
To your original question, I think the simple answer is as follows:
1) Have a relationship between UserUpload -> LoadStatus using LoanStatus column. I am not sure if you need the relationship between LoanStatus_CM and UserUpload as well for some other reason. But might be worth creating it.
2) Then the DAX measure to achieve your SQL syntax would be:
CALCULATE(CALCULATE(COUNT(UserUpload[Id]), FILTER(LoanStatus, [LoanStatusId] = [CM_LoanStatusId])), LoanStatus[CM_LoanStatusId] = 3)
Note the reply above is making some assumptions about the cardinalities of the tables and columns based on their names.