Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
I get 40.
Can you suggest anything based on the above? I am not sure where to look at.
@Anonymous
Please provide the sample data.
Are the columns types in DB and PBI same? Did you try the TRIM function in DAX?
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
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.
Hi, I did the whole thing from scratch and refresh everything and it seems to work now. Thanks for your help.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |