topic Re: Redshift data and Power BI data are not matching up in Developer

Redshift data and Power BI data are not matching up

Vysh27 — Tue, 15 Oct 2024 18:02:20 GMT

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!!

Re: Redshift data and Power BI data are not matching up

Anonymous — Wed, 16 Oct 2024 03:37:08 GMT

Hi @Vysh27 ,

It seems like there is data mismatch between AWS redshift and Power BI side when apply a date filter. 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

Re: Redshift data and Power BI data are not matching up

Vysh27 — Wed, 16 Oct 2024 05:07:28 GMT

Hi @Anonymous ,
- 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

Re: Redshift data and Power BI data are not matching up

Vysh27 — Wed, 16 Oct 2024 05:30:55 GMT

Hi @Anonymous ,Attaching ss without the filters

Re: Redshift data and Power BI data are not matching up

Anonymous — Wed, 16 Oct 2024 08:16:10 GMT

Hi @Vysh27 ,

According to your screenshot, it is true that 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 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 function you are applying on the field [guardian_id]? Count(Distinct) or Count?

Best Regards

Re: Redshift data and Power BI data are not matching up

Vysh27 — Wed, 16 Oct 2024 09:47:30 GMT

Here is the screenshot of the relationship and the aggregation

Re: Redshift data and Power BI data are not matching up

Anonymous — Thu, 17 Oct 2024 07:43:10 GMT

Hi @Vysh27 ,

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