Forum Discussion
Dax equavalent
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
7 Replies
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
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
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
