Convert SQL query into DAX query for PowerBI visual

Question

I have a table like below and trying to count the number of job_ids that are in "PAUSED" status in databricks and indicate it in PowerBI report. If there is job id that has both of "PAUSED" and "UNPAUSED" status with the same timestamp, I should count that job_id as well as it's in the "PAUSED" status.

table1

job_id	status	timestamp
A	PAUSED	2022-08-02T21:09:13
A	UNPAUSED	2022-08-02T21:09:13
B	PAUSED	2022-08-10T21:09:15
B	PAUSED	2022-07-20T21:09:13
A	PAUSED	2022-08-12T21:09:13
C	PAUSED	2022-08-01T21:07:19
C	PAUSED	2022-08-01T21:07:19
C	UNPAUSED	2022-08-03T21:07:19
B	UNPAUSED	2022-07-20T21:09:13
A	UNPAUSED	2022-08-04T21:09:13

and this is the sql query I wrote

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 and t2.status = "PAUSED"

What I want to do next is to display the number in PowerBI using the card visual. To do that I think I need to convert the sql query above into DAX query to get the correct number..(not sure if there is better way to perform it, I'm new to PowerBI) Anyone could help me with this query..?

Would appreciate any kind of help!

Answer 1

I hope This is what you want :

If we test the summary table:

Paused_Jobs =
VAR SummaryTable =
    ADDCOLUMNS (
        CALCULATETABLE (
            SUMMARIZE ( table1, table1[job_id], table1[status] ),
            table1[status] = "PAUSED"
        ),
        "Latest", CALCULATE ( VALUES ( table1[timestamp] ), LASTDATE ( table1[timestamp] ) ),
        "TotalCount", CALCULATE ( COUNTROWS ( table1 ), LASTDATE ( table1[timestamp] ) )
    )
VAR Result =
    SUMX ( SummaryTable, [TotalCount] )
RETURN
    SummaryTable

And the resulting screenshot:

You can check the result with sql query :

If It is what you want, and want it as a measure:

Paused_Jobs =
VAR SummaryTable =
    ADDCOLUMNS (
        CALCULATETABLE (
            SUMMARIZE ( table1, table1[job_id], table1[status] ),
            table1[status] = "PAUSED"
        ),
        "Latest", CALCULATE ( VALUES ( table1[timestamp] ), LASTDATE ( table1[timestamp] ) ),
        "TotalCount", CALCULATE ( COUNTROWS ( table1 ), LASTDATE ( table1[timestamp] ) )
    )
VAR Result =
    SUMX ( SummaryTable, [TotalCount] )
RETURN
    Result

Answer 2

In Power BI you would need the following measure:

Paused Jobs = 
CALCULATE(
    DISTINCTCOUNT('Table'[job_id]),
    'Table'[status] = "PAUSED"
)

You can then put this measure onto a Card visual next to your data table