topic Re: Converting SQL into DAX... in DAX Commands and Tips

Converting SQL into DAX...

yjk3140 — Thu, 25 Aug 2022 16:22:37 GMT

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

to get the number of paused jobs by getting the latest status of the each job_id. I have duplicate values in the table, so I can't use LASTDATE and MAX also didn't work to get the correct result.

Convert SQL query into DAX query for PowerBI visual - Stack Overflow

this is my question posted in StackOverflow.

the solutions I got don't meet my requirements 100%

Just wondering if anyone knows how to convert that sql query to DAX.

I'm doing this in direct query mode so there are some limitations as well..

thanks in advance!

Re: Converting SQL into DAX...

tamerj1 — Thu, 25 Aug 2022 19:55:32 GMT

Hi @yjk3140

please try

Paused Jobs = SUMX ( VALUES ( 'Table'[job_id] ), VAR MaxDate = MAX ( 'Table'[timestamp] ) VAR LastRecord = CALCULATETABLE ( 'Table', 'Table'[timestamp] = MaxDate ) VAR LastStatus = MAXX ( LastRecord, 'Table'[status] ) RETURN IF ( LastStatus = "PAUSED", 1 ) )

Re: Converting SQL into DAX...

yjk3140 — Fri, 26 Aug 2022 14:46:08 GMT

Hi @tamerj1, tanks for your resoponse 🙂

I tried your query, but it gives me blank as a result...

do you think this dax query makes sense to get the correct result?

Re: Converting SQL into DAX...

yjk3140 — Thu, 25 Aug 2022 21:12:19 GMT

Hi @tamerj1 , I think if I understood your query correctly..you are finding the latest timestamp in the table then count the job_ids are in paused status with that timestamp. please correct me if I understood it wrong.. but what i want is to find the max timestamp for each job_id then if the status of the job is in paused status with its latest timestamp then count it as 1.

Re: Converting SQL into DAX...

tamerj1 — Fri, 26 Aug 2022 02:44:53 GMT

@yjk3140

Apologies. It was too late last night and seems that I missed one small detail. Please use

Paused Jobs = SUMX ( VALUES ( 'Table'[job_id] ), VAR MaxDate = CALCULATE ( MAX ( 'Table'[timestamp] ) ) VAR LastRecord = CALCULATETABLE ( 'Table', 'Table'[timestamp] = MaxDate ) VAR LastStatus = MAXX ( LastRecord, 'Table'[status] ) RETURN IF ( LastStatus = "PAUSED", 1 ) )

and yes, for each job id we are finding the max date then we count it only if it is PAUSED

Re: Converting SQL into DAX...

yjk3140 — Fri, 26 Aug 2022 09:17:47 GMT

Hi @tamerj1! your DAX query looks logically correct and smart but I don't think MAX function works for the date column here..I'm not getting any result but blank.

Do you think there is another approach to solve this query that I can try?

Thank you so much for your help!

Re: Converting SQL into DAX...

tamerj1 — Fri, 26 Aug 2022 09:55:12 GMT

Hi @yjk3140
Just now I've prepared a sample file and it works just fine. However, this for sure depends of the filter context. The details that you might think they don't matter they actually do.

Re: Converting SQL into DAX...

yjk3140 — Fri, 26 Aug 2022 12:55:46 GMT

Hi @tamerj1 yes the query looks really clear but I'm not sure why it doesn't work on my dataset...

This is a screenshot of the actual table I have below

do you think the format of the date matters here..? if so, is there anyway to get the result I want with the timestamps in this format?

really appreciate your help..!

Re: Converting SQL into DAX...

yjk3140 — Fri, 26 Aug 2022 10:42:33 GMT

Hi @tamerj1 !

can we use count function intead of sum and if functions?

sth like this..(not sure if this query would make sense)

Paused Jobs =

VAR MaxDate =

MAX ( 'table1'[timestamp] )

VAR LastRecord =

CALCULATETABLE ( 'table1', 'table1'[timestamp] = MaxDate )

VAR LastStatus =

MAXX ( LastRecord, table1'[job_active_status] )

VAR countid = CALCULATE( DISTINCTCOUNT(ops_log[job_id]), LastStatus = "PUASED")

RETURN countid

Re: Converting SQL into DAX...

tamerj1 — Fri, 26 Aug 2022 10:44:49 GMT

@yjk3140
Is it a text or date format?

Re: Converting SQL into DAX...

yjk3140 — Fri, 26 Aug 2022 12:55:24 GMT

Hi @tamerj1

It's a date/time format!

Re: Converting SQL into DAX...

tamerj1 — Fri, 26 Aug 2022 10:52:01 GMT

@yjk3140
Please make sure you have this CALCULATE in your code

Re: Converting SQL into DAX...

yjk3140 — Fri, 26 Aug 2022 11:06:39 GMT

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? 😕

Re: Converting SQL into DAX...

tamerj1 — Fri, 26 Aug 2022 11:15:07 GMT

Hi @yjk3140
would you like to connect via teams or zoom? I have some time now

Re: Converting SQL into DAX...

yjk3140 — Fri, 26 Aug 2022 11:24:29 GMT

Hi @tamerj1 sure! I just created the zoom meeting and ID is 399 024 9510

Re: Converting SQL into DAX...

tamerj1 — Fri, 26 Aug 2022 11:28:45 GMT

@yjk3140
I'll join in 5min

Re: Converting SQL into DAX...

yjk3140 — Fri, 26 Aug 2022 11:29:56 GMT

sure thank you 🙂

Re: Converting SQL into DAX...

tamerj1 — Fri, 26 Aug 2022 11:31:44 GMT

@yjk3140

Asking for meeting passcode

Re: Converting SQL into DAX...

yjk3140 — Fri, 26 Aug 2022 14:47:02 GMT

///

Re: Converting SQL into DAX...

yjk3140 — Fri, 26 Aug 2022 12:15:45 GMT

Hi @tamerj1! could you join again? the meeting ended because of the subscription issue..I just started the same one