topic Re: In Need of Help to Convert SQL statement to DAX in DAX Commands and Tips

In Need of Help to Convert SQL statement to DAX

kkishba2 — Thu, 17 Oct 2024 18:58:11 GMT

I am new to Power BI and DAX. I need assistance converting (learning how to convert) a SQL statement to DAX. The statement below is useful extracting against SSMS, but we have a Semantic Model which pulls in all of the identified tables - and DAX is needed to create the report in PowerBI.

Any guidance would be helpful. Thank you.

Select W.Wrk + '-' + RIGHT(WS.Sample,2) as SampleID,
WS.SampleName,
WS.Sampled,
CONVERT(VARCHAR(12), SA.nResult) AS ReportedResult,
SA.Analyte,
CONVERT(VARCHAR(12),
SA.Result) AS InitialResult,
WS.SampledBy,
Case
When ws.sampledby = 'adam aleon'
Then 'AL'
Else sec.initials
End As Initials,
SA.Initials as ReviewedBy,
se.Status
From WRK as W
Inner Join WRKSAMPLE as WS On W.Wrk = WS.Wrk
Inner Join SAMPLEANALYSIS as SA On WS.Sample = SA.Sample And
WS.Wrk = SA.Wrk
Left Join SECURITY as Sec On Sec.Fullname = WS.SampledBy
left join sampleextraction as se on se.wrk = sa.wrk and se.sample = sa.sample and se.analysis = sa.analysis
Where W.ProjectNumber = 'p01072' and WS.Sampled >
--First day of last month
DATEADD(m,DATEDIFF(m,0,GETDATE())-1,0)
And WS.Sampled <
--First day of this month
DATEADD(ms,-2,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
And SA.Initials <> ''
And SA.IsQC = 0
And WS.SampleName Like 'S%'
And WS.SampleName Not Like '%A%'
And WS.SampleName Not Like '%B%'
And WS.SampleName Not Like 'SPV%'
And SE.status <> 'cancelled'
ORDER BY WS.Wrk, WS.Sample, SA.Analyte

Re: In Need of Help to Convert SQL statement to DAX

SachinNandanwar — Thu, 17 Oct 2024 19:41:53 GMT

Why not just create a SQL view of the above statement and feed that as an input to your semantic model ?

Re: In Need of Help to Convert SQL statement to DAX

kkishba2 — Thu, 17 Oct 2024 19:45:42 GMT

I am very new to this - so I'm not sure what that would look like (adding a view to the semantic model).

Re: In Need of Help to Convert SQL statement to DAX

SachinNandanwar — Thu, 17 Oct 2024 19:47:24 GMT

SQL View in your database.

https://en.wikipedia.org/wiki/View_(SQL)

Re: In Need of Help to Convert SQL statement to DAX

kkishba2 — Thu, 17 Oct 2024 19:52:47 GMT

Thank you. I was hoping to learn how this would translate to DAX - so that I can begin to understand the language and be able to code for myself in the future.

Re: In Need of Help to Convert SQL statement to DAX

SachinNandanwar — Thu, 17 Oct 2024 19:59:24 GMT

Thats great but unforutanetly at the first sight the SQL query looks to complicated to be converted to DAX .There are 4 tables involved with multiple joins with to many predicates.If its me I would stick to SQL View approach.

But if you still would like to try the DAX way, then post the semantic model,the business rules and the sample data here.

Re: In Need of Help to Convert SQL statement to DAX

dvraan — Fri, 18 Oct 2024 02:26:42 GMT

selectdax.in