Forum Discussion

yvese's avatar
yvese
Frequent Visitor
8 years ago

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_LoanStatusId = tblLoanStatus_CM.LoanStatusId INNER JOIN
	dbo.tblUserUpload ON dbo.tblLoanStatus.LoanStatus = dbo.tblUserUpload.LoanStatus

WHERE 
	dbo.tblLoanStatus.CM_LoanStatusId = 3

I tried using a nested CROSSJOIN, but I'm getting an error that Argument '6' in SELECTCOLUMNS function is required. 

This is my code below

 

Total Status = 

SELECTCOLUMNS(
	FILTER(
		CROSSJOIN(tblUserUpload,
			FILTER(
				CROSSJOIN(
				
					summarize( 
						addcolumns( 
						tblLoanStatus,
						"Cmyers Status",
						tblLoanStatus[CM_LoanStatusId]),
						[Cmyers Status]), 
				
				
					
					summarize( 
						tblLoanStatus,
						tblLoanStatus[LoanStatusId])),
					
					tblLoanStatus[CM_LoanStatusId] = tblLoanStatus[LoanStatusId]				
			
					)
				),
		tblUserUpload[LoanStatus]=tblLoanStatus[LoanStatus]
              ),
	"Client", tblLoanStatus[ClientId],
	"Internal Id", tblLoanStatus[CM_LoanStatusId],

			)

 

 

I can provide extra information if needed!!

 

Thanks in advance!

 

2 Replies

  • srinivt's avatar
    srinivt
    Microsoft Employee
    8 years ago

    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.

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi yvese,

     

    Is srinivt's suggestion helpful to your scenario? If so, would you please mark the helpful reply as an answer? If not, please provide sample data in tblLoanStatus and tblUserUpload and make table structure more clear. Also, please post a screenshot of the result returned by above SQL Query so that we can understand your expected output.

     

    Regards,

    Yuliana Gu