Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Power BI Distinct producing different result than SQL Distinct

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.

 

 

 

Need Help

4 Replies

  • VasTg's avatar
    VasTg
    Icon for Memorable Member rankMemorable Member
    6 years ago

    Anonymous 

    Please provide the sample data.

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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.