How to replicate given sql logic in powerbi..... (Migrating from SSRS to Powerbi)

[Azhar_]

Hi all,
We are migrating SSRS reports to powerbi. I have this specific query on which i have spent a lot of time replicating it in powerbi.

SELECT comp_CompanyID FROM vPRBBOSCompanyList

WHERE comp_CompanyID IN

(SELECT CASE WHEN prcr_LeftCompanyID=@CompanyId THEN prcr_RightCompanyID ELSE prcr_LeftCompanyID END As CompanyID

FROM PRCompanyRelationship WITH (NOLOCK)

WHERE (prcr_LeftCompanyID = u@CompanyId OR prcr_RightCompanyID=@CompanyId)

AND prcr_Type IN (27, 28, 29)

AND prcr_Active = 'Y'

AND prcr_Deleted IS NULL)

AND comp_PRListingStatus IN ('L', 'H', 'LUV')

AND LocalSourceStateID IS NULL

OR LocalSourceStateID IN (SELECT DISTINCT prlsr_StateID FROM PRLocalSourceRegion WITH (NOLOCK) WHERE prlsr_ServiceCode IN ('LSSLIC'))

before jumping to replicate all the logic in powerbi, I am trying just this part to be executed first

SELECT comp_CompanyID FROM vPRBBOSCompanyList

WHERE comp_CompanyID IN

(SELECT CASE WHEN prcr_LeftCompanyID=@CompanyId THEN prcr_RightCompanyID ELSE prcr_LeftCompanyID END As CompanyID

FROM PRCompanyRelationship WITH (NOLOCK)

I tried creating a calculated column in the PRCompanyRelationship table. But i found that calculated columns are not dynamic paramter selections wont change the values of the calculated column.

Tried creating a measure but issue is we cannot create measures using columns they have to be aggregated in comparision

Only solution i can think of now is to write custom sql. Using power query parameter .

Can anybody give me a better soloution if it exist??

Thanks.

[rajendraongole1]

Hi @Azhar_  - Converting complex SQL logic like this into Power BI can be challenging , can you try the below approach as mentioned 

Instead of calculated columns, use measures for dynamic filtering based on slicer or parameter values.

Create a new table in Power BI to represent the @CompanyId parameter

CompanyParameter = GENERATESERIES(1, 1000, 1) -- Replace 1-1000 with valid CompanyIDs

Implement the CASE Logic in a Measure: Use DAX to replicate the CASE logic:

SelectedCompany =
IF(
SELECTEDVALUE(PRCompanyRelationship[prcr_LeftCompanyID]) = SELECTEDVALUE(CompanyParameter[Value]),
PRCompanyRelationship[prcr_RightCompanyID],
PRCompanyRelationship[prcr_LeftCompanyID]
)

Filter the Data for use the measure to filter the vPRBBOSCompanyList table:

IsCompanyValid =
IF(
vPRBBOSCompanyList[comp_CompanyID] IN VALUES(SelectedCompany),
1,
0
)

Use the IsCompanyValid measure to filter your visuals or tables in Power BI. I hope this will helps you. try the above approach.