Dax equavalent

[MatsyaaAvatar]

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]

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

[v-yiruan-msft]

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]

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

[v-yiruan-msft]

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

[MatsyaaAvatar]

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

[MatsyaaAvatar]

Here is a sample data. 

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

[MatsyaaAvatar]

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