Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nataliesmiy1357
Helper III
Helper III

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.