Forum Discussion
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) / 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
11 Replies
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.
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.
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
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 NameBest Regards,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.
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
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.
