Forum Discussion
Converting query into Data model in Power Bi Desktop
Hi Everyone,
I have a report with the following query as source.
SELECT
PM.Description 'Provider Name',
PM.Address_line_1,
PM.City,
PM.State,
national_provider_ID 'NPI',
IM.Create_timestamp,
P.Practice_Name
FROM dbo.intrf_provider_mstr IM
INNER JOIN Provider_mstr PM
ON IM.internal_rec_id = PM.Provider_ID
LEFT JOIN provider_practice_mstr PPM
ON PM.Provider_ID = PPM.Provider_ID
LEFT JOIN Practice P
ON PPM.PRactice_ID = P.Practice_ID
WHERE external_system_id = '90005'
AND internal_rec_ID <> 'BD3A1435-FDDC-42E7-8EF5-B5CE1AA79C89'
AND ppm.practice_id <> '0001' --Default
Instead of this query I need to load the tables sepeartely and create data modelling in power bi desktop(relationships).Applied all the filters while loading the tables itself.
I have created the model like following image
But the data is not filtering as expected.
Visual with the query
Visual with tables
Practice name is from Practice Table and Providers is from Provider_mstr.
Can anyone help me whats the issue here
Hi harikrishnang2,
Thank you for reaching out to the Microsoft Fabric Forum Community. And special thanks to johnt75 for promt and helpful response.
The issue is happening because the filters you applied in your original SQL query (like removing a specific provider ID, default practice ID, and filtering by external system ID) were not applied when loading the individual tables into Power BI. As a result, extra or unmatched data is getting included, which breaks the relationships and causes the visuals to show incorrect provider counts. To fix this, you should apply the same filters in Power BI’s Power Query Editor while loading the tables, make sure the relationships are correctly set (with matching keys and proper direction), and use the provider_practice_mstr table for counting providers instead of directly using the Provider_mstr table. This will make your visuals match the output from the SQL query.
Best regards,
Prasanna Kumar
8 Replies
Change all the relationships to be single direction so that the one side filters the many side. Bi-directional relationships can be useful in certain niche cases but as a general rule they should be single direction.
Tried that already, but it's not working
Its possible that some rows which are being filtered out in the original query because of the joins are not being filtered when you import the tables directly and then apply filters.
You could rewrite the queries which bring the separate tables in to all use the same joins and where conditions, then the same data will come into the model as you get by running the single query.
Hi harikrishnang2,
Thank you for reaching out to the Microsoft Fabric Forum Community. And special thanks to johnt75 for promt and helpful response.
The issue is happening because the filters you applied in your original SQL query (like removing a specific provider ID, default practice ID, and filtering by external system ID) were not applied when loading the individual tables into Power BI. As a result, extra or unmatched data is getting included, which breaks the relationships and causes the visuals to show incorrect provider counts. To fix this, you should apply the same filters in Power BI’s Power Query Editor while loading the tables, make sure the relationships are correctly set (with matching keys and proper direction), and use the provider_practice_mstr table for counting providers instead of directly using the Provider_mstr table. This will make your visuals match the output from the SQL query.
Best regards,
Prasanna KumarHi harikrishnang2,
Just following up to see if the solutions provided were helpful in addressing the issue.
If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.
Best regards,
Prasanna KumarHi harikrishnang2,
Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.
Best regards,
Prasanna Kumar
Hi Prasanna,
I've fixed the issue, Thank you for your assistance
Hi harikrishnang2,
Thank you for reaching out to the Microsoft Fabric Forum Community.
Great to hear that the issue is resolved! If one of the responses helped, kindly consider marking it as the Accepted Solution. Alternatively, if you found a different solution on your own, please do share it and mark that post as the accepted one. This will be helpful for others in the community facing similar issues.
Best regards,
Prasanna Kumar
