topic Re: Dax equavalent in Developer

Dax equavalent

MatsyaaAvatar — Mon, 10 Oct 2022 15:15:25 GMT

Good morning!

I am New to Dax. How can i do this in Dax ?

SELECT Interval_attempts AS attempts,
(VSF/Interval_attempts)*100 as VSF_pct,
FROM (
SELECT
count(*) AS Interval_attempts,
SUM ( case WHEN (startup_error>0 and playingtime=0) then 1 else 0 end) as VSF,
FROM tablex WHERE ssd_date = date )a

Re: Dax equavalent

Anonymous — Tue, 11 Oct 2022 03:20:01 GMT

Hi @MatsyaaAvatar ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create two measures as below to get it:

Attempts = COUNTROWS(ALLSELECTED('tablex'))

VSF_pct = VAR _VSF = CALCULATE ( COUNT ( 'tablex'[playingtime] ), FILTER ( 'tablex', 'tablex'[startup_error] > 0 && 'tablex'[playingtime] = 0 ) ) RETURN DIVIDE ( _VSF, [Attempts] )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Re: Dax equavalent

MatsyaaAvatar — Tue, 11 Oct 2022 13:14:49 GMT

yingyinr, Thank you very much. I am going to check this out and let you know if this works. But this is a great start and I can take it from here.

Re: Dax equavalent

MatsyaaAvatar — Tue, 11 Oct 2022 15:54:40 GMT

I am attempting a more complex one this time.. in similiar lines. Any pointers ?

SELECT convert(decimal(20,4), Interval_buffering_time*100/(Interval_buffering_time + Interval_playing_time)) as buffering_ratio

FROM (

SELECT

ROUND((SUM(case when a.buffering_time_ms >30*60*1000 then 30*60*1000 else a.buffering_time_ms - isnull(b.prior_buffering_time_ms,0) end )/60000),2) AS Interval_buffering_time,

ROUND((SUM(a.buffering_time_ms)/60000),2) AS life_buff_time ,

ROUND((SUM(a.playing_time_ms - isnull(b.prior_playing_time_ms,0) )/60000),2) AS Interval_playing_time

FROM (

SELECT playing_time_ms,

buffering_time_ms,

conviva_session_id,

startup_time_ms,

start_time_unix_time,

ssd

FROM conviva_qoe_data WHERE ssd = '2022/10/08' AND startup_time_ms!=-1) a

LEFT outer JOIN (

SELECT r.conviva_session_id AS prior_session_id,

r.buffering_time_ms AS prior_buffering_time_ms,

r.playing_time_ms AS prior_playing_time_ms,

r.startup_time_ms as p_startuptime,

r.start_time_unix_time as p_starttime

FROM conviva_qoe_Data r

WHERE r.ssd = '2022/10/09' ) b

ON (a.conviva_session_id = b.prior_session_id and a.start_time_unix_time =b.p_starttime)

where a.playing_time_ms>0

) Tmp

Re: Dax equavalent

Anonymous — Wed, 12 Oct 2022 01:56:33 GMT

Hi @MatsyaaAvatar ,

Could you please provide some sample data in the table conviva_qoe_Data (exclude sensitive data) with Text format, the result for the above SQL query in SQL Server and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file with connected data. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Re: Dax equavalent

MatsyaaAvatar — Wed, 12 Oct 2022 13:57:01 GMT

Here is a sample data.

https://drive.google.com/drive/folders/110eadqbJmkI7bo1aElrNNbgRpHwNv9zo?usp=sharing

Re: Dax equavalent

MatsyaaAvatar — Thu, 13 Oct 2022 15:37:55 GMT

Hello, I've uploaded the sample data. Anybody able to help with this ? I seem to be struck unable to figure out nested sqls to DAX

Re: Dax equavalent

MatsyaaAvatar — Mon, 17 Oct 2022 17:39:05 GMT

Hello !! Can someone help with the above conversion to DAX ?