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.