Forum Discussion

Massingc1983's avatar
Massingc1983
Frequent Visitor
2 years ago
Solved

Error when trying to export large data from Power bi

Hi 

 

I am trying to export a large amount of data from a power bi table (above the max row limit). 

 

I am using DAX studio and have copied the DAX query from the performance analyser however when i run it i get the following error:

 

A single value for column in table cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregations such as min, max, count, or sum

 

I have copied my DAX query below. Please could somebody help 

 

// DAX Query
DEFINE
    VAR __DS0FilterTable = 
        TREATAS({"PO"}, 'Append All'[Method])

    VAR __DS0FilterTable2 = 
        FILTER(
            KEEPFILTERS(VALUES('Append All'[Payment Date])),
            AND(
                'Append All'[Payment Date] >= DATE(2023, 5, 1),
                'Append All'[Payment Date] < DATE(2024, 6, 20)
            )
        )

    VAR __DS0Core = 
        SUMMARIZECOLUMNS(
            'Append All'[Vendor],
            'Append All'[Vendor Name],
            'Append All'[SAP Vendor Name],
            __DS0FilterTable,
            __DS0FilterTable2,
            "MinPO", CALCULATE(MIN('Append All'[PO])),
            "MinInvoice_Date", CALCULATE(MIN('Append All'[Invoice Date])),
            "MinPosting_Date", CALCULATE(MIN('Append All'[Posting Date])),
            "MinPayment_Date", CALCULATE(MIN('Append All'[Payment Date])),
            "SumNet_Invoice", CALCULATE(SUM('Append All'[Net Invoice])),
            "MinPGr", CALCULATE(MIN('Append All'[PGr]))
        )

EVALUATE
    __DS0FilterTable

ORDER BY
    'Append All'[Vendor], 'Append All'[Vendor Name], 'Append All'[SAP Vendor Name]

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Massingc1983 

     

    Thanks for the reply from DataInsights .

     

    You can try changing the formula to the following:

    VAR __DS0FilterTable2 = 
            FILTER(
                KEEPFILTERS(VALUES('Append All'[Payment Date])),
                AND(
                    MAX('Append All'[Payment Date]) >= DATE(2023, 5, 1),
                    MAX('Append All'[Payment Date]) < DATE(2024, 6, 20)
                )
            )

     

    Best Regards,
    Yulia Xu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Massingc1983 

     

    Thanks for the reply from DataInsights .

     

    You can try changing the formula to the following:

    VAR __DS0FilterTable2 = 
            FILTER(
                KEEPFILTERS(VALUES('Append All'[Payment Date])),
                AND(
                    MAX('Append All'[Payment Date]) >= DATE(2023, 5, 1),
                    MAX('Append All'[Payment Date]) < DATE(2024, 6, 20)
                )
            )

     

    Best Regards,
    Yulia Xu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.