Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

SQL query to DAX

Hi ALL I am trying to transform SQL query into DAX language but unable to do so. Please look at the query and help me with the DAX ************ SELECT CAST(CAST(Songs.SwedishCount AS DECIMAL) / CA...
Need Help
Icey's avatar
Icey
Community Support
6 years ago

Hi Anonymous ,

 

Please check:

Percent foreign songs played =
VAR maxversion =
    CALCULATE ( MAX ( 'TrackData'[Version] ) )
VAR p =
    CALCULATETABLE (
        DISTINCT ( 'Episode'[Id] ),
        FILTER (
            'Episode',
            'Episode'[TablaTjansteId]
                IN CALCULATETABLE (
                    DISTINCT ( 'ProgramService'[TablaTjansteId] ),
                    'ProgramService'[Name] = "P3"
                )
                && 'Episode'[Datum] >= DATE ( 2020, 3, 9 )
                && 'Episode'[Datum] <= DATE ( 2020, 3, 16 )
                && HOUR ( [StartTidUtc] ) >= 5
                && ROUNDDOWN ( 'Episode'[SlutTidUtc], 0 ) = ROUNDDOWN ( 'Episode'[StartTidUtc], 0 )
        )
    )
VAR tid =
    CALCULATETABLE (
        DISTINCT ( 'MusicListRow'[TrackId] ),
        FILTER (
            'MusicListRow',
            'MusicListRow'[MusicListId]
                IN CALCULATETABLE (
                    DISTINCT ( 'MusicList'[Id] ),
                    FILTER ( MusicList, 'MusicList'[EpisodeId] IN p )
                )
        )
    )
VAR td =
    FILTER (
        'TrackData',
        'TrackData'[TrackId] IN p
            && 'TrackData'[Version] = maxversion
            && 'TrackData'[Value] IN { "Ja", "SE" }
            && 'TrackData'[Name]
            IN { "Gramark.Svensk", "GramarkLive.Svensk", "Musikbank.Swedish" }
    )
RETURN
    COUNTROWS ( FILTER ( td, NOT ( 'TrackData'[Value] IN { "Ja", "SE" } ) ) )
        / COUNTROWS ( td )

Percent swedish songs played =
VAR maxversion =
    CALCULATE ( MAX ( 'TrackData'[Version] ) )
VAR p =
    CALCULATETABLE (
        DISTINCT ( 'Episode'[Id] ),
        FILTER (
            'Episode',
            'Episode'[TablaTjansteId]
                IN CALCULATETABLE (
                    DISTINCT ( 'ProgramService'[TablaTjansteId] ),
                    'ProgramService'[Name] = "P3"
                )
                && 'Episode'[Datum] >= DATE ( 2020, 3, 9 )
                && 'Episode'[Datum] <= DATE ( 2020, 3, 16 )
                && HOUR ( [StartTidUtc] ) >= 5
                && ROUNDDOWN ( 'Episode'[SlutTidUtc], 0 ) = ROUNDDOWN ( 'Episode'[StartTidUtc], 0 )
        )
    )
VAR tid =
    CALCULATETABLE (
        DISTINCT ( 'MusicListRow'[TrackId] ),
        FILTER (
            'MusicListRow',
            'MusicListRow'[MusicListId]
                IN CALCULATETABLE (
                    DISTINCT ( 'MusicList'[Id] ),
                    FILTER ( MusicList, 'MusicList'[EpisodeId] IN p )
                )
        )
    )
VAR td =
    FILTER (
        'TrackData',
        'TrackData'[TrackId] IN p
            && 'TrackData'[Version] = maxversion
            && 'TrackData'[Value] IN { "Ja", "SE" }
            && 'TrackData'[Name]
            IN { "Gramark.Svensk", "GramarkLive.Svensk", "Musikbank.Swedish" }
    )
RETURN
    COUNTROWS ( FILTER ( td, 'TrackData'[Value] IN { "Ja", "SE" } ) )
        / COUNTROWS ( td )

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Icey 

Thankyou very much for your help.  I tried it but it gives me Blank. I ran different variables separately and it runs, but as a whole it gives blank result.

 

I have added a Name column that was missing in "TrackData" table. and uploaded a new file which link is here

https://1drv.ms/u/s!AgrolwBG08XGjRgvhU_hIj17ZMwI?e=stn4Y1

 

I have also created two columns from last part of query which is 

TrackData'[Value] IN { "Ja", "SE" }

 But i am able to solve only half of query with this method.   You can give it a try if you can. or I will continue to work on this with the help of your query.. 

THanks

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Based on my test, the cause is that there are no records that satisfy the conditions at the same time.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thankyou very much for double check

      I will try to load more rows to check if there's record, because there are more than 2 million records and i only loaded half million, 

      Can you suggest me how to think like this as you did in your solution. ? Thought process ? because i was not able to think like this 

      any course or suggestions to improve thinking about how to build DAX from SQL query.

       

      • Icey's avatar
        Icey
        Community Support
        6 years ago

        Hi Anonymous ,

         

        Sorry to reply late.

        It is necessary to understand the calculation logic of SQL query firstly. And, some DAX functions are needed to understand. Then, convert it to DAX according to the calculation logic.

         

        In DAX, "VAR" stores the result of an expression as a named variable, which can then be passed as an argument to other measure expressions.

        Reference: DAX: Use variables to improve your formulas.

         

        Please let me know if the expressions above work.

         

         

        Best regards

        Icey

         

        If this post helps,then please consider accepting it as the solution to help other members find it more quickly.