Forum Discussion
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]
- Anonymous2 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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- DataInsightsSuper User
- AnonymousNot 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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.