Forum Discussion

Vysh27's avatar
Vysh27
New Member
1 year ago

Redshift data and Power BI data are not matching up

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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

Vysh27's avatar
Vysh27
New Member
1 year ago

Here is the screenshot of the relationship and the aggregation

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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