Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am following a Tutorial (https://docs.microsoft.com/en-us/power-bi/paginated-reports/report-builder-shared-datasets) and it states "If your query includes the TOPN function, delete it from your query". However, when I try, I must be deleting too much. How do I know EXACTLY which text to delete?
Thanks, cheers!
// DAX Query
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('Date'[Month])),
NOT('Date'[Month] IN {"October",
"November",
"December"})
)
VAR __DS0FilterTable2 =
TREATAS({"Domestic"}, 'Customers'[Sales Division])
VAR __DS0FilterTable3 =
FILTER(
KEEPFILTERS(VALUES('SSM'[SalesPerson Territory])),
NOT('SSM'[SalesPerson Territory] IN {""})
)
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
'Focus Products'[Item Category], "IsGrandTotalRowTotal",
'Focus Products'[Item Family], "IsDM1Total",
'Focus Products'[Product Code-Desc], "IsDM2Total"
),
'Date'[Year],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
"SumQuantity", CALCULATE(SUM('Invoice Details'[Quantity]))
)
VAR __DS0PrimaryWindowed =
TOPN(
102,
SUMMARIZE(
__DS0Core,
'Focus Products'[Item Category],
[IsGrandTotalRowTotal],
'Focus Products'[Item Family],
[IsDM1Total],
'Focus Products'[Product Code-Desc],
[IsDM2Total]
),
[IsGrandTotalRowTotal],
0,
'Focus Products'[Item Category],
1,
[IsDM1Total],
1,
'Focus Products'[Item Family],
1,
[IsDM2Total],
1,
'Focus Products'[Product Code-Desc],
1
)
VAR __DS0SecondaryBase =
SUMMARIZE(__DS0Core, 'Date'[Year])
VAR __DS0Secondary =
TOPN(102, __DS0SecondaryBase, 'Date'[Year], 1)
VAR __DS0BodyLimited =
NATURALLEFTOUTERJOIN(
__DS0PrimaryWindowed,
SUBSTITUTEWITHINDEX(
__DS0Core,
"ColumnIndex",
__DS0Secondary,
'Date'[Year],
ASC
)
)
EVALUATE
__DS0Secondary
ORDER BY
'Date'[Year]
EVALUATE
__DS0BodyLimited
ORDER BY
[IsGrandTotalRowTotal] DESC,
'Focus Products'[Item Category],
[IsDM1Total],
'Focus Products'[Item Family],
[IsDM2Total],
'Focus Products'[Product Code-Desc],
[ColumnIndex]
Another option would be to connect to your dataset from Report Builder and then use the Query Designer there to build the dataset you need (instead of copy/paste of the DAX from Power BI).
Pat
Hi @Unglaublichusa ,
After removing TOPN function, the DAX query would be like:
DEFINE
VAR __DS0FilterTable =
FILTER (
KEEPFILTERS ( VALUES ( 'Date'[Month] ) ),
NOT ( 'Date'[Month] IN { "October", "November", "December" } )
)
VAR __DS0FilterTable2 =
TREATAS ( { "Domestic" }, 'Customers'[Sales Division] )
VAR __DS0FilterTable3 =
FILTER (
KEEPFILTERS ( VALUES ( 'SSM'[SalesPerson Territory] ) ),
NOT ( 'SSM'[SalesPerson Territory] IN { "" } )
)
VAR __DS0Core =
SUMMARIZECOLUMNS (
ROLLUPADDISSUBTOTAL (
'Focus Products'[Item Category],
"IsGrandTotalRowTotal",
'Focus Products'[Item Family],
"IsDM1Total",
'Focus Products'[Product Code-Desc],
"IsDM2Total"
),
'Date'[Year],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
"SumQuantity", CALCULATE ( SUM ( 'Invoice Details'[Quantity] ) )
)
VAR __DS0PrimaryWindowed =
SUMMARIZE (
__DS0Core,
'Focus Products'[Item Category],
[IsGrandTotalRowTotal],
'Focus Products'[Item Family],
[IsDM1Total],
'Focus Products'[Product Code-Desc],
[IsDM2Total]
)
VAR __DS0SecondaryBase =
SUMMARIZE ( __DS0Core, 'Date'[Year] )
VAR __DS0BodyLimited =
NATURALLEFTOUTERJOIN (
__DS0PrimaryWindowed,
SUBSTITUTEWITHINDEX (
__DS0Core,
"ColumnIndex", __DS0SecondaryBase,
'Date'[Year], ASC
)
)
EVALUATE
__DS0SecondaryBase
ORDER BY 'Date'[Year]
EVALUATE
__DS0BodyLimited
ORDER BY
[IsGrandTotalRowTotal] DESC,
'Focus Products'[Item Category],
[IsDM1Total],
'Focus Products'[Item Family],
[IsDM2Total],
'Focus Products'[Product Code-Desc],
[ColumnIndex]
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey @Unglaublichusa ,
I don't think you really need to remove all of the TOPN variables listed in the query you can just change your first evaluate
EVALUATE
__DS0Secondary
to
EVALUATE
__DS0SecondaryBase
With that said you can also try this...
// DAX Query
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('Date'[Month])),
NOT('Date'[Month] IN {"October",
"November",
"December"})
)
VAR __DS0FilterTable2 =
TREATAS({"Domestic"}, 'Customers'[Sales Division])
VAR __DS0FilterTable3 =
FILTER(
KEEPFILTERS(VALUES('SSM'[SalesPerson Territory])),
NOT('SSM'[SalesPerson Territory] IN {""})
)
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
'Focus Products'[Item Category], "IsGrandTotalRowTotal",
'Focus Products'[Item Family], "IsDM1Total",
'Focus Products'[Product Code-Desc], "IsDM2Total"
),
'Date'[Year],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
"SumQuantity", CALCULATE(SUM('Invoice Details'[Quantity]))
)
VAR __DS0PrimaryWindowed =
SUMMARIZE(
__DS0Core,
'Focus Products'[Item Category],
[IsGrandTotalRowTotal],
'Focus Products'[Item Family],
[IsDM1Total],
'Focus Products'[Product Code-Desc],
[IsDM2Total]
)
VAR __DS0SecondaryBase =
SUMMARIZE(__DS0Core, 'Date'[Year])
VAR __DS0BodyLimited =
NATURALLEFTOUTERJOIN(
__DS0PrimaryWindowed,
SUBSTITUTEWITHINDEX(
__DS0Core,
"ColumnIndex",
__DS0Secondary,
'Date'[Year],
ASC
)
)
EVALUATE
__DS0SecondaryBase
ORDER BY
'Date'[Year]
EVALUATE
__DS0BodyLimited
ORDER BY
[IsGrandTotalRowTotal] DESC,
'Focus Products'[Item Category],
[IsDM1Total],
'Focus Products'[Item Family],
[IsDM2Total],
'Focus Products'[Product Code-Desc],
[ColumnIndex]
Proud to be a Super User!
I am only getting one of the Table Fields with this change.
Error Message on Validate; "Query (54, 1) Failed to resolve name '<pii>__DS0Secondary</pii>'. It is not a valid table, variable, or function name."
Hey @Unglaublichusa , change __DS0Secondary to
__DS0SecondaryBase
Proud to be a Super User!
Hi Watsky,
This helped in solving my problem of TOPN issue. Thank you.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.