Power BI Distinct producing different result than SQL Distinct

[Anonymous]

Hi There

I have a table with column NAME and I am trying to find the total count of distinct values from NAME column. If for that I run the following SQL query 

select distinct RTRIM(LTRIM(Name)) from [MyTable]
WHERE NAME IS NOT NULL
AND START_DATE BETWEEN '01/07/2019' AND '31/07/2019'
;

I get 45 columns. Looking at the data, this is the correct value.

But when I am trying to get this from PowerBI distinct using the following 

UniqueNumber = CALCULATE (COUNTROWS(DISTINCT(MyTable[Name])))

I get 40.

Can you suggest anything based on the above? I am not sure where to look at.

[VasTg]

@Anonymous 

Please provide the sample data.

Are the columns types in DB and PBI same? Did you try the TRIM function in DAX?

[Anonymous]

Thanks

Sample data from both SQL and Power BI will look like

BM_NY_L5-06 Koorda
BM_NY_Mobile
BM_NY_Chairmans_Office
BM_NY_Lv19_Officer_Rm
BM_NY_Lv20_Sports_Rm

Are the columns types in DB and PBI same?

>>> Yes, both are same, SQL Server Varchar 200 and Power BI Text

Did you try the TRIM function in DAX?

>>> NO, the Name column are as it is used in Power BI

[Anonymous]

Just in case this helps:

I noticed if I change my slicer on Power BI to 2/12/2018 to 31/07/2019, it shows 45 as count even in Power BI. 

But from 3/12/2018 it is gradually reducing to 40.

Whereas in my datbase if I use filter like 

START_DATE BETWEEN '01/07/2019' AND '31/07/2019'

It is still showing 45.

[Anonymous]

Hi, I did the whole thing from scratch and refresh everything and it seems to work now. Thanks for your help.