Dax equavalent

MatsyaaAvatar · ‎10-10-2022

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

MatsyaaAvatar · ‎10-11-2022

yingyinr, Thank you very much. I am going to check this out and let you know if this works. But this...

Anonymous · ‎10-10-2022

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

MatsyaaAvatar · ‎10-11-2022

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

Anonymous · ‎10-11-2022

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