sqldatabasepowerbifinanceaccounting

Working on a SQL Function for excluding account segment

I am building a visual in Power BI that visualizes different lines of business. The accounts that I am working with are built out in different segments in the database (c_Seg1, c_Seg2, c_Seg3.. etc.) I am trying to exclude values for a specific segment (c_Seg7, also known as Dim7 in one of these accounts. Seg7 is a column in the database I am using. I also have 3 detail groups that filter the account c_detailgroup = 'Group 1' 'Group 2', 'Group 3'.

Here is my SQL query, it does not pull the values that it is supposed to:

SELECT CASE
           WHEN gl.Scenario = 1 THEN 'Actuals'
           WHEN gl.Scenario = 6 THEN 'Budget'
           ELSE 'UNKNOWN'
       END AS Scenario ,
       *
FROM [database].[dbo].[f_Trans_GL] gl
LEFT JOIN database.dbo.d_Dim0 d0 ON gl.Dim0 = d0.CustomerId
LEFT JOIN database.dbo.d_Dim8 d8 ON gl.Dim8 = d8.CustomerId
WHERE c_Seg7 NOT IN ('001','002','003','004')
   AND gl.Scenario IN (1,
                       6)
   AND gl.Entity = 1
   AND c_detail group = 'Group 1'
   OR c_detail group = 'Group 2'
   OR c_detail group = 'Group 3'

This does not pull in the correct information, I tried filtering Seg7 in Power BI, but still did not return the correct value.

I tried different variations of excluding the segment amounts, including:

SELECT CASE
           WHEN gl.Scenario = 1 THEN 'Actuals'
           WHEN gl.Scenario = 6 THEN 'Budget'
           ELSE 'UNKNOWN'
       END AS Scenario ,
       *
FROM [database].[dbo].[f_Trans_GL] gl
LEFT JOIN database.dbo.d_Dim0 d0 ON gl.Dim0 = d0.CustomerId
LEFT JOIN database.dbo.d_Dim8 d8 ON gl.Dim8 = d8.CustomerId
   AND gl.Scenario IN (1,
                       6)
   AND gl.Entity = 1
   AND c_detail group = 'Group 1'
   OR c_detail group = 'Group 2'
   OR c_detail group = 'Group 3'
   AND NOT c_Seg7 = '001'
   AND NOT c_Seg7 = '002'
   AND NOT c_Seg7 = '003'

I have also tried filtering the data in Power BI to get the correct value, with no luck.

Solution

  • You should place the AND condition within parentheses to ensure the proper logical grouping. You also need to use AND instead of OR for the c_detail group condition to filter records where it's either 'Group 1', 'Group 2', or 'Group 3'.

    SELECT CASE
           WHEN gl.Scenario = 1 THEN 'Actuals'
           WHEN gl.Scenario = 6 THEN 'Budget'
           ELSE 'UNKNOWN'
       END AS Scenario ,
       *
FROM [database].[dbo].[f_Trans_GL] gl
LEFT JOIN database.dbo.d_Dim0 d0 ON gl.Dim0 = d0.CustomerId
LEFT JOIN database.dbo.d_Dim8 d8 ON gl.Dim8 = d8.CustomerId
WHERE gl.Scenario IN (1, 6)
   AND gl.Entity = 1
   AND (c_detail group = 'Group 1'
        OR c_detail group = 'Group 2'
        OR c_detail group = 'Group 3')
   AND c_Seg7 NOT IN ('001', '002', '003', '004');