Redshift data and Power BI data are not matching up

Question

Hi Everyone , I have been facing an issue with the data in power BI which comes from AWS redshift
I'm validating the count of distinct guardians for a particular date with AWS redshift data.
I have used direct query mode as the method for loading the data.
The total no of distinct guardians showed in power BI is matching up with total no of distinct guardians in AWS redshift but when I add a date filter and then check there seems to be a bit higher count in Power BI than redshift.
Need some help with why there is higher count in Power BI.

If Someone can help me with ASAP it would be great as I am stuck on the same from the past 4 days.
Thankss!!

anonymous · Answer

Hi&nbsp;Vysh27&nbsp;,
Thanks for your reply. Please follow the steps below to get it:
1. Create a measure as below
Measure = 
CALCULATE (
    DISTINCTCOUNT ( 'final_phones'[guardian_id] ),
    FILTER (
        'final_phones',
        'final_phones'[organization_id]
            IN VALUES ( 'trs_organizations'[organization_id] )
    )
)
2. Put the above measure to replace the aggregation function: Count of guardian_id. And check if it can return same count records with AWS Redshift...

Best Regards

anonymous · Answer

Hi&nbsp;Vysh27&nbsp;,
It seems like there is data mismatch between AWS redshift and Power BI side when apply a date filter.&nbsp; Could you please provide the following info?

Please provide the "filter" setting(slicer or filter pane).
What's the date format of date field? What's the timezone?
Is there any NULL values for this date field?
Could you please provide some records(especially date field should be specific to the hour, minute, and second ) which display in Power BI but not display in AWS Redshift in the same date period?

Best Regards

vysh27 · Answer

Hi&nbsp;Anonymous&nbsp; ,&nbsp;- I have used the date filter in the filter pane- It's Just date format- No there aren't any null values-I have attached the details below please have a look&nbsp;

vysh27 · Answer

Hi&nbsp;Anonymous&nbsp;,Attaching ss without the filters&nbsp;

anonymous · Answer

Hi&nbsp;Vysh27&nbsp;,
&nbsp;According to your screenshot, it is true that&nbsp; there are more pieces of data inside Power BI than inside AWS redshift on the day Oct 01, 2024. Did you create any relationship among these table&nbsp;transforms.final_phones, transforms.trs_organizations and transforms.trs_dates in Power BI? Could you please provide your relationship settings with screenshot? By the way, which aggregation&nbsp;function you are applying on the field [guardian_id]? Count(Distinct) or Count?