Forum Discussion

Azhar_'s avatar
Azhar_
New Member
1 year ago

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

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.

Need Help

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    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.