The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
36 | |
23 | |
23 | |
21 | |
16 |