Appended Data Showing in Query View Not In Report

[NMC20]

I have two Excel spreadsheets as my data source - 'Paypal' data and 'Stripe' data. 

In Power BI, I've managed to append as new to create All Payment Data and, in this table, I can see all the data combined and in the correct fields. 

However, the figures in my report are only calculating the Stripe Data fields, not Paypal. I have checked that I am using the All Payment Data fields in this report.

I also have some calculated columns in All Payment Data which bring back information about each venue based on a table called 'Sites Information'. All of these fields are coming back for Stripe and Paypal transactions. However, I also have a field which calculates a % based on when the transaction was made at which site but these are returning blank for Paypal. So my basic Lookups work for Paypal but not the Lookup based on an if statement. I didn't know if this helped indicate the problem?

Is there an obvious reason the Paypal data is being ignored in my report?

For info:

This is the formula not working for Paypal transactions

% Revenue =

VAR InstallationDate = RELATED('Sites Information'[Installation Date])

VAR ThreeMonthsDate = RELATED('Sites Information'[3 Months Date])

VAR First3MonthsPercentage = RELATED('Sites Information'[First %])

VAR After3MonthsPercentage = RELATED('Sites Information'[3 Months %])

RETURN

    IF(

        [Date of Order] >= InstallationDate && [Date of Order] < ThreeMonthsDate,

        First3MonthsPercentage,

        IF(

            [Date of Order] >= ThreeMonthsDate,

            After3MonthsPercentage,

            BLANK() -- Or use 0 or any default value if needed

        )

    )

These are my relationships

Sites information > Stripe Data (1:* on [Location])

Sites information > Paypal (1:* on [Location])

Sites information > All Payment Data (1:* on [Location])

All Payment Data <> Stripe Data (1:1 on [Transaction ID])

All Payment Data <> Payment Data (1:1 on [Transaction ID])

[NMC20]

I deleted the relationship between 'Stripe' and 'Paypal' to 'Sites Information' which appears to fix the issue, so now only 'All Payment Data' links to 'Sites Information'. 

It has now broken the variable listed above which is returning an Error for all transactions. 

I don't understand why because there is still the relationship between the All Payment Data table (where I want the field to be) and Sites Information (where I'm getting the data from). Apologies, analysis isn't my role (I just think it's useful) so I'm slow to catch on.

[danextian]

If the appended data is in the table/data view then the problem could be with your measure. If it isn't there, verify that there are no extra steps prior to loading that removes the appended data.

[rajendraongole1]

Hi @NMC20  - Double-check that your IF statement logic is correctly handling the conditions for both Paypal and Stripe. You may want to add a condition to handle the case where RELATED returns blank

Modified one, you can try below:

% Revenue =
VAR InstallationDate = RELATED('Sites Information'[Installation Date])
VAR ThreeMonthsDate = RELATED('Sites Information'[3 Months Date])
VAR First3MonthsPercentage = RELATED('Sites Information'[First %])
VAR After3MonthsPercentage = RELATED('Sites Information'[3 Months %])
RETURN
IF(
ISBLANK(InstallationDate) || ISBLANK(ThreeMonthsDate) || ISBLANK(First3MonthsPercentage) || ISBLANK(After3MonthsPercentage),
BLANK(), -- or a default value like 0 if needed
IF(
[Date of Order] >= InstallationDate && [Date of Order] < ThreeMonthsDate,
First3MonthsPercentage,
IF(
[Date of Order] >= ThreeMonthsDate,
After3MonthsPercentage,
BLANK()
)
)
)

[NMC20]

Thanks for the response!

I don't think this would solve my problem because my report is entirely excluding Paypal data, even on fields which aren't blank in the query table (e.g. transaction data) so I suspect I've appended the data incorrectly or my relationships aren't right - I'm afraid I just don't understand it enough to understand what I've not done correctly. 

[rajendraongole1]

you can try by select the All Payment Data query.Ensure all PayPal data rows are present after the append operation.If rows are missing, revisit the append settings and ensure both data sources are correctly included.

Columns from both sources must match exactly in name and data type. Mismatched column names or types can result in missing data.

Temporarily remove relationships involving All Payment Data.Create a table visual with All Payment Data fields to see if PayPal data appears. If it does, the issue is with relationships; if not, the issue is with the append process.

check these and let me know still issue exist please share pbix file by removing confidential data.

[NMC20]

In producing a sanitised version to post on here, I've discovered the problem. 

One of the steps within my "Transform Data" stage is that, based on a booking reference ID, Power BI tells me what site this transaction relates to (my [Location] field). The results from this definitely match on Paypal, Stripe and All Payment Data because both Paypal and Stripe return the correct data on a lookup to All Payment Data on [Location] field. 

When I've manually typed fake locations in to the Excel document to create fake data (so no formula based on Booking ID needed) the report brings everything back so it must be something about this [Location] field which is throwing the report.

Obviously I can create a workaround of an Excel formula to work out the location prior to Power BI but I'd like to avoid this if possible. Is there an obvious reason I'm unaware of which would make the correct data show in the table stage but not pull through to the report due to this formula?

Hope that makes sense!