Forum Discussion
Solved
Converting SQL into DAX...
Hi, I have written the sql query below SELECT count(job_id) FROM (SELECT distinct job_id,status,DENSE_RANK over() partition by job_id order by timestamp desc AS rn FROM table1) t2 WHERE t2.rn = 1 a...
Hi yjk3140
Final solution is as followsPaused Jobs = SUMX ( VALUES ( 'table1'[job_id] ), VAR MaxDate = CALCULATE ( MAX ( 'table1'[datetime1] ) ) VAR LastRecord = FILTER ( CALCULATETABLE ( 'table1' ), 'table1'[datetime1] = MaxDate && 'table1'[job_active_status] = "PAUSED" ) VAR Result = IF ( NOT ISEMPTY ( LastRecord ), 1 ) RETURN Result )Anonymous
Ok, please tryCASH_IPD = COUNTROWS ( CALCULATETABLE ( SUMMARIZE ( BI_OSR_Revenues, BI_OSR_Revenues[Encounter_No], BI_OSR_Revenues[Payment_TypeName], BI_OSR_Revenues[InvoiceNo] ), BI_OSR_Revenues[Tran_Month] = 3, BI_OSR_Revenues[Tran_Year] = 2022, BI_OSR_Revenues[Payment_TypeName] = "CASH (IPD)", NOT ISBLANK ( BI_OSR_Revenues[admission_Date] ) ) )
Hi tamerj1 ,
I tried to use this query below to count the # of the jobs
Paused Jobs =
VAR MaxDate =
CALCULATE(MAX ( 'table1'[timestamp] ))
VAR LastRecord =
CALCULATETABLE ( 'table1', 'table1'[timestamp] = MaxDate )
VAR LastStatus =
MAXX ( LastRecord, 'table1[job_status] )
VAR countid = CALCULATE( DISTINCTCOUNT(table1[job_id]), LastStatus = "PUASED")
RETURN countid
and it's throwing the error below
The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column
How can I fix it? 😕
sure thank you 🙂
///