Paginated Reports only looking at the first 501 rows

[Anonymous]

Hi,

I have built a summary report of customers, month and outstanding amount, 

This is linked to a Powerbi data model (in premium), the connection works fine however the query in Paginated Reports only returns 501 rows of data and summarizes that.   Below is the DAX I am using from Powerbi to import into Paginated Reports

// DAX Query
DEFINE
    VAR __DS0FilterTable =
        TREATAS ( { "Y" }, 'WPA Shipments'[CarrierInvoice(Y/N)] )
    VAR __DS0FilterTable2 =
        TREATAS (
            { "ACCEPTING_DEBRIEF_RECEIVED_FROM_EXECUTING_SITE" },
            'WPO Data'[DeBriefStatus]
        )
    VAR __DS0FilterTable3 =
        TREATAS ( { "" }, 'WPO Data'[InvoiceStatus] )
    VAR __DS0FilterTable4 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'WPO Data'[OfferingOrderOwner] ) ),
            NOT ( 'WPO Data'[OfferingOrderOwner] IN { "" } )
        )
    VAR __DS0FilterTable5 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'WPC Calendar'[Date] ) ),
            'WPC Calendar'[Date] >= DATE ( 2020, 1, 1 )
        )
    VAR __ValueFilterDM0 =
        FILTER (
            KEEPFILTERS (
                SUMMARIZECOLUMNS (
                    'WPA Shipments'[ShipmentNumber],
                    'WPA ShipmentTimestamps'[JobFileClosed],
                    'WPO Data'[DeBriefStatus],
                    'WPO Data'[Shipment XID],
                    'WPO Data'[InvoiceStatus],
                    'WPO Data'[InvoiceDate],
                    'WPO Data'[OfferingOrderOwner],
                    __DS0FilterTable,
                    __DS0FilterTable2,
                    __DS0FilterTable3,
                    __DS0FilterTable4,
                    __DS0FilterTable5,
                    "Total_Revenue", 'MEASURES (2)'[Total Revenue]
                )
            ),
            [Total_Revenue] <> 0
        )
    VAR __DS0Core =
        SUMMARIZECOLUMNS (
            'WPA Shipments'[ShipmentNumber],
            'WPA ShipmentTimestamps'[JobFileClosed],
            'WPO Data'[DeBriefStatus],
            'WPO Data'[Shipment XID],
            'WPO Data'[InvoiceStatus],
            'WPO Data'[InvoiceDate],
            'WPO Data'[OfferingOrderOwner],
            __DS0FilterTable,
            __DS0FilterTable2,
            __DS0FilterTable3,
            __DS0FilterTable4,
            __DS0FilterTable5,
            __ValueFilterDM0,
            "Total_Revenue", 'MEASURES (2)'[Total Revenue]
        )
    VAR __DS0PrimaryWindowed =
        TOPN (
            501,
            __DS0Core,
            'WPA Shipments'[ShipmentNumber], 1,
            'WPA ShipmentTimestamps'[JobFileClosed], 1,
            'WPO Data'[DeBriefStatus], 1,
            'WPO Data'[Shipment XID], 1,
            'WPO Data'[InvoiceStatus], 1,
            'WPO Data'[InvoiceDate], 1,
            'WPO Data'[OfferingOrderOwner], 1
        )

EVALUATE
__DS0PrimaryWindowed
ORDER BY
    'WPA Shipments'[ShipmentNumber],
    'WPA ShipmentTimestamps'[JobFileClosed],
    'WPO Data'[DeBriefStatus],
    'WPO Data'[Shipment XID],
    'WPO Data'[InvoiceStatus],
    'WPO Data'[InvoiceDate],
    'WPO Data'[OfferingOrderOwner]

When I compare datasets from both paginated reports and PowerBI the powerbi model has 1,932 rows which summarizes perfectly in Powerbi versus only the first 501 rows in Paginated Reports.

Does anyone know why this might be happening and how I work around?

thanks 

[lbendlin · Accepted solution]

Change your DAX to remove the row count filter

[Anonymous]

Thanks so much, obvious now when I look at it....  thanks again.