how to implement below query in power bi

[basim]

how to implement this query in power bi

select epu.[Project Status] INTO #temp from pjrep.MSP_EpmProject_UserView epu (nolock) where
([Project Departments] = @ProjectDepartmentName or @ProjectDepartmentName = '-1') and
([Concept Type] = @ConceptType or @ConceptType = '-1') and
([Primary Technology Department] = @PrimaryTechnologyDepartment or @PrimaryTechnologyDepartment = '-1') and
([Business Sponsor] like '%' + @CommercialProductOwner + '%' or @CommercialProductOwner = '-1')

select
(select count(*) from #temp where [Project Status] = 'On Schedule' ) as Green,
(select count(*) from #temp where [Project Status] = 'Not Started' or [Project Status] = 'Possible Delayed' ) as Yellow,
(select count(*) from #temp where [Project Status] = 'Delayed' or [Project Status] = 'Suspended' ) as Red

[v-jiascu-msft]

Hi @basim,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[basim · Accepted solution]

The proper answer is below :

 Use CALCULATE function and get (Project Status) data in PIE chart.

Get all parameters into three drop downlists.

then drag and drop Project Status on PIE Chart. 

[v-jiascu-msft]

Thank you so much for sharing with us. @basim,

Best Regards,

Dale

[v-jiascu-msft]

Hi @basim,

Seems you want a DAX formula. It could be like below. But the details are based on your data. If you need it, please provide a sample data.

#temp = filter(
selectcolumns(msp_epmproject_userview, 
"Project Status", [project status]), 
([project departments] = @ProjectDepartmentName 
          OR @ProjectDepartmentName = '-1' ) 
&& ( [concept type] = @ConceptType OR @ConceptType = '-1' ) 
&& ( [primary technology department] = @PrimaryTechnologyDepartment OR @PrimaryTechnologyDepartment = '-1' ) 
&& ( [business sponsor] LIKE '%' + @CommercialProductOwner + '%' OR @CommercialProductOwner = '-1' ) 

Best Regards,

Dale

[Anonymous]

Hi @basim,

You can use this SQL query directly in Power BI.

Go To > 

Get Data > Sql Server > Advance options > Put your Sql Query.

Make sure you have last statement as select * from #temp , so that you query should return something.

Thanks,

Jayant