SQL query to DAX

[Anonymous]

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) / CAST(Songs.TotalCount AS DECIMAL) * 100 AS DECIMAL(5,2)) AS 'Percent swedish songs played',
CAST(CAST(Songs.ForeignCount AS DECIMAL) / CAST(Songs.TotalCount AS DECIMAL) * 100 AS DECIMAL(5,2)) AS 'Percent foreign songs played'
FROM

( SELECT SUM(CASE WHEN td.[Value] = 'Ja' OR td.[Value] = 'SE' THEN 1 ELSE 0 END) AS SwedishCount,
SUM(CASE WHEN td.[Value] <> 'Ja' AND td.[Value] <> 'SE' THEN 1 ELSE 0 END) AS ForeignCount,
COUNT(*) AS TotalCount
FROM MusicList ml

JOIN MusicListRow mlr
ON ml.id = mlr.MusicListId
JOIN TrackData td
ON mlr.TrackId = td.TrackId

WHERE td.[Name] IN ('Gramark.Svensk', 'GramarkLive.Svensk', 'Musikbank.Swedish')
AND ml.EpisodeId IN (
SELECT e.id
FROM episode e
JOIN ProgramService P ON E.TablaTjansteId=P.TablaTjansteId
WHERE p.[Name] = 'P3'
AND e.Datum between '2020-03-09' AND '2020-03-15'
AND CAST(DATEADD(HOUR, 1, e.StartTidUtc) AS TIME) >= '06:00' -- DATEADD for utc to local in march
AND CAST(DATEADD(HOUR, 1, e.SlutTidUtc) AS DATE) = CAST(DATEADD(HOUR, 1, e.StartTidUtc) AS DATE) -- Has to be the same day
)
AND td.[Version] IN ( -- Get highest version
SELECT MAX(td2.[Version])
FROM TrackData td2
WHERE td2.TrackId = td.TrackId
)
) AS Songs

 *****************************************************

THere are five tables that are joined together ( Episode, TrackData, ProgramService, MusicList, MusicListRow)

these are joined with ID's with each other. Resutlt in SQL looks like this as under

THanks

[Icey]

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]

@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]

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]

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]

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.

[Anonymous]

@Icey 

Its fine with delay, as i am working another project side by side. anyway, I tried your solution separately like each part of long DAX and it works but when i run whole code it gives me blank .  SQL code run on sql server gives data , I am unable to know at this point about why it is not giving me data. 

I tried to remove date restriction in VAR P  **&& 'Episode'[Datum] >= DATE ( 2020; 3; 9 )
                                                     ** && 'Episode'[Datum] <= DATE ( 2020; 3; 16 )

but result was the same BLANK.  I even increase the data load to 1m rows,  what do  you think what is the problem.?

[Icey]

Hi @Anonymous ,

Here are multiple posts about transforming SQL to DAX. Hope they would help you.

Or, you may share me some dummy sample data, removing sensitive information, for test. You can upload your file to OneDrive for business and paste the link here.

Best Regard,

Icey

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

[Anonymous]

Hi @Icey 

here is the link of my PBI file. 

I have tried different tables and measures, you can remove all extra to start from beginning.

Best regards

[Icey]

Hi @Anonymous ,

Please create some dummy data, not real data, based on the following fields:

ml   MusicList:            id, EpisodeId
mlr  MusicListRow:     MusicListId  TrackID
td    TrackData:           TrackID Version Value
e     episode:               TablaTjansteId  Datum StartTidUtc
p     ProgramService:  TablaTjansteId  Name

Best Regards,

Icey

[Anonymous]

Hi

THankyou for your input. 

Please find attached PBI file with "dummy data"..

Note that I have created a Bridge table "Trackid-Unique" because there were duplicate rows of this column in both MusiclistRow and Trackdata tables.., so that i can create relationship between these two.

Data modeling is done and now I need a help in solving the SQL query into Dax.

https://1drv.ms/u/s!AgrolwBG08XGjRe7LCGHTdGF-A1A?e=XaOrSK

THanks and appreciate all help

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490, with particular attention to the section about posting SQL and asking for the DAX equivalent.