cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nataliesmiy1357
Helper II
Helper II

SQL statement to a new column or measure

Hello!  I am trying to figure out this code with my report.  I was given this SQL statement that is used in reporting, but I'm not sure how to connect them.  This query is to determine the "step" for this process. I'm not sure how to create a new column or measure using this code.  Thanks in advance.  I've attached the code and a screenshot of the data so far (basically everything in the 8 needs to be broken out into confirmed, evaluation, and post-work).  

 

ALTER FUNCTION [dbo].[fnGetThemeStepInProgress] (@ThemeId INT)
RETURNS VARCHAR(15)
AS
BEGIN
DECLARE @stepId Varchar(15)
DECLARE @stepNumber Varchar(15)
select @stepNumber = ISNULL(cast(MAX(stepid) as Varchar(15)),'Pre-Work')
from tblThemeStepDate where startdate is not null and themeid = @ThemeId


IF (@stepNumber = '8' and ( select count(1) from tblThemeStepDate
where enddate is not null and themeid = @ThemeId and stepid = 8)>0 )
SET @stepId = 'Post-Work'
ELSE If @stepNumber = 'Pre-Work'
SET @stepId = 'Pre-Work'
Else IF (( select count(1) from tblThemeStepDate
where enddate is not null and themeid = @ThemeId and stepid = @stepId)>0 )
SET @stepId = 'Not Available'
ELSE IF (( select count(1) from tblThemeStepDate
where startdate is not null and
enddate is not null and themeid = @ThemeId and stepid = @stepNumber)=1 )

SET @stepId = CONCAT('Step ',@stepNumber)
Else IF (( select count(1) from tblThemeStepDate
where startdate is not null and
enddate is null and themeid = @ThemeId and stepid = @stepNumber)=1 )

SET @stepId = CONCAT('Step ',@stepNumber)
else
SET @stepId = CONCAT('Step ',@stepNumber)

IF (@stepId = 'Post-Work')
IF ((select count(1) from tblThemePreAndPostWork where LevelAssesCompDate is not null
and themeId = @ThemeId)>0)
IF ((select count(1) from tblThemeEvaluationDetail where PlantPromotionDate is not null and
themeId = @ThemeId)>0)
SET @stepId = 'Confirmed'
ELSE
SET @stepId = 'Evaluation'
ELSE
SET @stepId = 'Post-Work'

RETURN @stepId
END

 

nataliesmiy1357_0-1638553969364.png

 

3 REPLIES 3
jeroendekk
Resolver IV
Resolver IV

Hi @nataliesmiy1357 
Measures and calculated column use DAX. You cannot use SQL. The only place where you can use SQL in Power BI is in the Query when connecting to a SQL datasource (or when working with paginated reports).

aaimg.jpg
I am not sure the SQL statement will work here. But you could try. I do think it possible to recreate the logic in either M or DAX.

But that would only be possible if you know what you want, whats the bussiness logic is. It is almost impossible for someone here to translate a SQL statement into a Power BI solution without knowing the data/bussiness requirements.

Hope's this helps!
Jeroen-
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Greg_Deckler
Super User
Super User

@nataliesmiy1357 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, 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

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

I don't know the output that's the problem - I've been given this query in SQL and told that I need to use that formula to calulate that table correctly.  I just don't know how to input a query into a new column to determind the type.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!