Power BI result is different from SQL result

[Anonymous]

Hello gurus,
My sql query result is different from that of my Power BI result. I have a simple query which just pulls the data based on the date entered. Power BI filter shows that it is pulling from the same date as I queried in sql. However, the Power BI result is based on data queried from the full data count.  I have my inner joins and relationship set up in Power BI. Attached are examples of what I'm referring to...

SQL query:

SELECT *

FROM myFacilitySite site
INNER JOIN myQuarantineCase mq ON site.fkeyCaseID = mq.ID
INNER JOIN myCareFacility mcf ON mq.FacilityID = mcf.ID
INNER JOIN lkuClearance lclr ON site.ClearanceStatusId = lclr.ID
WHERE mq.EntryDate BETWEEN '1/1/2022' AND '7/23/2022'
AND lclr.ID = 2 OR lclr.ID = 3
AND mcf.IsActive = 1
GROUP BY mcf.[Name]
ORDER BY COUNT(site.ID) DESC, acf.[Name]

SQL output:

Power BI DAX Studio:

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    FILTER(
      KEEPFILTERS(VALUES('myQuarantineCase'[EntryDate])),
      AND(
        'myQuarantineCase'[EntryDate] >= DATE(2022, 1, 1),
        'myQuarantineCase'[EntryDate] < DATE(2022, 7, 23)
      )
    )
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      'Metrics - Animals On-Hold'[myCareFacility.Name],
      __DS0FilterTable,
      "CountACF_Count", CALCULATE(COUNTA('Metrics - Animals On-Hold'[ACF Count]))
    )
  VAR __DS0PrimaryWindowed = 
    TOPN(
      1001,
      __DS0Core,
      [CountCareFacility_Count],
      0,
      'Metrics - Animals On-Hold'[myCareFacility.Name],
      1
    )
EVALUATE
  __DS0PrimaryWindowed
ORDER BY
  [CountCareFacility_Count] DESC,
  'Metrics - Animals On-Hold'[myCareFacility.Name]

Power BI result - notice the values as compared to the SQL values:

Thanks for you help.

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

I think some mistake on your SQL statement. The priority of  AND and OR is different.

Please try this sql to check if the same:

SELECT 
  * 
FROM 
  myFacilitySite site 
  INNER JOIN myQuarantineCase mq ON site.fkeyCaseID = mq.ID 
  INNER JOIN myCareFacility mcf ON mq.FacilityID = mcf.ID 
  INNER JOIN lkuClearance lclr ON site.ClearanceStatusId = lclr.ID 
WHERE 
  mq.EntryDate BETWEEN '1/1/2022' 
  AND '7/23/2022' 
  AND (
    lclr.ID = 2 
    OR lclr.ID = 3
  ) 
  AND mcf.IsActive = 1 
GROUP BY 
  mcf.[Name] 
ORDER BY 
  COUNT(site.ID) DESC, 
  acf.[Name]

Or you can change the mode of your table to DirectQuery and use the performance analyzer, copy the query and you will get  // Direct Query which is sql statement.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Also, regardless of the date entered (1/1/2020 - 1/1/2022 or 1/1/2021 - 1/1/2022 or even 1/1/2020 - 1/1/2022), the result is still the same as displayed above. A suggestion will really be helpful. Thanks.

[SpartaBI]

@Anonymous you can PM me to do a quick zoom call and we could look together if you want

[Anonymous]

What is your Zoom Id?

[SpartaBI]

@Anonymous I'm so sorry 🙂 I missed your message and now just saw it cause @v-chenwuz-msft reply poped it in my notifications 🙂

[SpartaBI]

@Anonymous The BETWEEN operator in SQL is inclusive: begin and end values are included and I see in your DAX query that you have < from the end date. Try change to <= and let me know.

      

Showcase Report – Contoso By SpartaBI

[Anonymous]

It does not change a thing. The result is the same. Thanks.