Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
airwolf39
Helper V
Helper V

Why is the drill down arrow so much faster than the "+" buttons?

I am doing the exact same thing.

  • Why is the drill down arrow so fast?
  • Why does the "+" get hung up? The visual wont load in a premium workspace... thats crazy.GIF 8-1-2022 12-35-57 PM.gif
3 REPLIES 3
airwolf39
Helper V
Helper V

Look at all the "Not(isempty)" code being generated by Power BI when clicking the + button. That is brutal....

airwolf39
Helper V
Helper V

The dax queries are different!

 

The drill down:

// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"AutoZone"}, 'Customer Name'[Business Name Major])

VAR __DS0FilterTable2 =
TREATAS({"Receiving",
"Confirmed"}, 'RM_KEY Table'[Return Status])

VAR __DS0FilterTable3 =
TREATAS({"Stock Adjustment",
"Lifts"}, 'RM_KEY Table'[Return Type])

VAR __DS0FilterTable4 =
TREATAS({"AM"}, 'Customer Name'[AM/OE/IC])

VAR __DS0FilterTable5 =
TREATAS(
{"'Customer Name'[Business Name Major]",
"'Customer Name'[Customer Name_ MT]"},
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories Fields]
)

VAR __DS0FilterTable6 =
FILTER(KEEPFILTERS(VALUES('Date Table'[Year])), 'Date Table'[Year] > 2020)

VAR __DS0FilterTable7 =
TREATAS({"Y"}, 'RM_KEY Table'[RM Flag])

VAR __DS0FilterTable8 =
TREATAS({"N",
BLANK()}, 'Parts'[ddp_flag])

VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
'Customer Name'[Business Name Major], "IsGrandTotalRowTotal",
'Customer Name'[Customer Name_ MT], "IsDM1Total"
),
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
__DS0FilterTable5,
__DS0FilterTable6,
__DS0FilterTable7,
__DS0FilterTable8,
"v_InventoryValue_RM", '1. Measures'[$InventoryValue_RM],
"RM_Qty", '1. Measures'[RM_Qty],
"Credit_Value_RM", '1. Measures'[Credit_Value_RM],
"v_Scrap", '1. Measures'[$Scrap],
"Returns_Processed_Qty", 'Returns - RT'[Returns_Processed_Qty],
"Credit_Value_RT", 'Returns - RT'[Credit_Value_RT],
"Returns_Qty_Delta", '1. Measures'[Returns_Qty_Delta],
"Customer_Credit_Value_Delta", '1. Measures'[Customer_Credit_Value_Delta],
"v_InventoryValue_RT", 'Returns - RT'[$InventoryValue_RT],
"Inventory_Value_Delta", '1. Measures'[Inventory_Value_Delta],
"MinReturn_Status", IGNORE(CALCULATE(MIN('RM_KEY Table'[Return Status])))
)

VAR __DS0CoreTableByDM1 =
SELECTCOLUMNS(
KEEPFILTERS(FILTER(KEEPFILTERS(__DS0Core), [IsDM1Total] = TRUE)),
"IsGrandTotalRowTotal", [IsGrandTotalRowTotal],
"'Customer Name'[Business Name Major]", 'Customer Name'[Business Name Major],
"SortBy_DM1_0", [v_InventoryValue_RM]
)

VAR __DS0PrimaryWithSortColumns =
NATURALLEFTOUTERJOIN(
__DS0Core,
__DS0CoreTableByDM1
)

VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0PrimaryWithSortColumns,
[IsGrandTotalRowTotal],
0,
[SortBy_DM1_0],
0,
'Customer Name'[Business Name Major],
1,
[IsDM1Total],
0,
[v_InventoryValue_RM],
0,
'Customer Name'[Customer Name_ MT],
1
)

VAR __DS0CoreNoInstanceFiltersNoTotals =
FILTER(KEEPFILTERS(__DS0Core), AND([IsGrandTotalRowTotal] = FALSE, [IsDM1Total] = FALSE))

EVALUATE
GROUPBY(
__DS0CoreNoInstanceFiltersNoTotals,
"MinRM_Qty", MINX(CURRENTGROUP(), [RM_Qty]),
"MaxRM_Qty", MAXX(CURRENTGROUP(), [RM_Qty]),
"Minv_InventoryValue_RM", MINX(CURRENTGROUP(), [v_InventoryValue_RM]),
"Maxv_InventoryValue_RM", MAXX(CURRENTGROUP(), [v_InventoryValue_RM]),
"MinCredit_Value_RM", MINX(CURRENTGROUP(), [Credit_Value_RM]),
"MaxCredit_Value_RM", MAXX(CURRENTGROUP(), [Credit_Value_RM]),
"Minv_Scrap", MINX(CURRENTGROUP(), [v_Scrap]),
"Maxv_Scrap", MAXX(CURRENTGROUP(), [v_Scrap]),
"MinReturns_Processed_Qty", MINX(CURRENTGROUP(), [Returns_Processed_Qty]),
"MaxReturns_Processed_Qty", MAXX(CURRENTGROUP(), [Returns_Processed_Qty]),
"MinCredit_Value_RT", MINX(CURRENTGROUP(), [Credit_Value_RT]),
"MaxCredit_Value_RT", MAXX(CURRENTGROUP(), [Credit_Value_RT]),
"MinReturns_Qty_Delta", MINX(CURRENTGROUP(), [Returns_Qty_Delta]),
"MaxReturns_Qty_Delta", MAXX(CURRENTGROUP(), [Returns_Qty_Delta]),
"MinCustomer_Credit_Value_Delta", MINX(CURRENTGROUP(), [Customer_Credit_Value_Delta]),
"MaxCustomer_Credit_Value_Delta", MAXX(CURRENTGROUP(), [Customer_Credit_Value_Delta]),
"Minv_InventoryValue_RT", MINX(CURRENTGROUP(), [v_InventoryValue_RT]),
"Maxv_InventoryValue_RT", MAXX(CURRENTGROUP(), [v_InventoryValue_RT]),
"MinInventory_Value_Delta", MINX(CURRENTGROUP(), [Inventory_Value_Delta]),
"MaxInventory_Value_Delta", MAXX(CURRENTGROUP(), [Inventory_Value_Delta])
)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
[IsGrandTotalRowTotal] DESC,
[SortBy_DM1_0] DESC,
'Customer Name'[Business Name Major],
[IsDM1Total] DESC,
[v_InventoryValue_RM] DESC,
'Customer Name'[Customer Name_ MT]


// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS(
{"'Customer Name'[Business Name Major]",
"'Customer Name'[Customer Name_ MT]"},
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories Fields]
)

VAR __DS0Core =
CALCULATETABLE(
SUMMARIZE(
'2. Returns-Open: Catagories',
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories Fields],
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories Order],
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories]
),
KEEPFILTERS(__DS0FilterTable)
)

VAR __DS0BodyLimited =
TOPN(
152,
__DS0Core,
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories Order],
1,
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories Fields],
1,
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories],
1
)

EVALUATE
__DS0BodyLimited

ORDER BY
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories Order],
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories Fields],
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories]

 

The + Button:

// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"Receiving",
"Confirmed"}, 'RM_KEY Table'[Return Status])

VAR __DS0FilterTable2 =
TREATAS({"Stock Adjustment",
"Lifts"}, 'RM_KEY Table'[Return Type])

VAR __DS0FilterTable3 =
TREATAS({"AM"}, 'Customer Name'[AM/OE/IC])

VAR __DS0FilterTable4 =
TREATAS(
{"'Customer Name'[Business Name Major]",
"'Customer Name'[Customer Name_ MT]",
"'Parts'[Catalog #]"},
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories Fields]
)

VAR __DS0FilterTable5 =
FILTER(KEEPFILTERS(VALUES('Date Table'[Year])), 'Date Table'[Year] > 2020)

VAR __DS0FilterTable6 =
TREATAS({"Y"}, 'RM_KEY Table'[RM Flag])

VAR __DS0FilterTable7 =
TREATAS({"N",
BLANK()}, 'Parts'[ddp_flag])

VAR __DS0CoreNoInstanceFilters =
SELECTCOLUMNS(
KEEPFILTERS(
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
'Customer Name'[Business Name Major], "IsGrandTotalRowTotal",
'Customer Name'[Customer Name_ MT], "IsDM1Total",
'Parts'[Catalog #], "IsDM3Total"
),
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
__DS0FilterTable5,
__DS0FilterTable6,
__DS0FilterTable7,
"v_InventoryValue_RM", '1. Measures'[$InventoryValue_RM],
"RM_Qty", '1. Measures'[RM_Qty],
"Credit_Value_RM", '1. Measures'[Credit_Value_RM],
"v_Scrap", '1. Measures'[$Scrap],
"Returns_Processed_Qty", 'Returns - RT'[Returns_Processed_Qty],
"Credit_Value_RT", 'Returns - RT'[Credit_Value_RT],
"Returns_Qty_Delta", '1. Measures'[Returns_Qty_Delta],
"Customer_Credit_Value_Delta", '1. Measures'[Customer_Credit_Value_Delta],
"v_InventoryValue_RT", 'Returns - RT'[$InventoryValue_RT],
"Inventory_Value_Delta", '1. Measures'[Inventory_Value_Delta],
"MinReturn_Status", CALCULATE(MIN('RM_KEY Table'[Return Status])),
"HasDataOpen_SO", IGNORE(
CALCULATE(NOT(ISEMPTY('Open SO')))
),
"HasDataPK_Table", IGNORE(
CALCULATE(NOT(ISEMPTY('PK Table')))
),
"HasDataFill_Rate_Shipments", IGNORE(
CALCULATE(NOT(ISEMPTY('Fill Rate Shipments')))
),
"HasDataInvoiced_Dollars", IGNORE(
CALCULATE(NOT(ISEMPTY('Invoiced Dollars')))
),
"HasDataForecast", IGNORE(
CALCULATE(NOT(ISEMPTY('Forecast')))
),
"HasDataReturns___RT", IGNORE(
CALCULATE(NOT(ISEMPTY('Returns - RT')))
),
"HasDataReturns___RM", IGNORE(
CALCULATE(NOT(ISEMPTY('Returns - RM')))
)
)
),
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
NOT(ISBLANK([v_InventoryValue_RM])),
NOT(ISBLANK([RM_Qty]))
),
NOT(ISBLANK([Credit_Value_RM]))
),
NOT(ISBLANK([v_Scrap]))
),
NOT(ISBLANK([Returns_Processed_Qty]))
),
NOT(ISBLANK([Credit_Value_RT]))
),
NOT(ISBLANK([Returns_Qty_Delta]))
),
NOT(ISBLANK([Customer_Credit_Value_Delta]))
),
NOT(ISBLANK([v_InventoryValue_RT]))
),
NOT(ISBLANK([Inventory_Value_Delta]))
),
OR(
OR(
OR(
OR(
OR(OR([HasDataOpen_SO], [HasDataPK_Table]), [HasDataFill_Rate_Shipments]),
[HasDataInvoiced_Dollars]
),
[HasDataForecast]
),
[HasDataReturns___RT]
),
[HasDataReturns___RM]
)
)
)
),
"'Customer Name'[Business Name Major]", 'Customer Name'[Business Name Major],
"'Customer Name'[Customer Name_ MT]", 'Customer Name'[Customer Name_ MT],
"'Parts'[Catalog #]", 'Parts'[Catalog #],
"IsGrandTotalRowTotal", [IsGrandTotalRowTotal],
"IsDM1Total", [IsDM1Total],
"IsDM3Total", [IsDM3Total],
"v_InventoryValue_RM", [v_InventoryValue_RM],
"RM_Qty", [RM_Qty],
"Credit_Value_RM", [Credit_Value_RM],
"v_Scrap", [v_Scrap],
"Returns_Processed_Qty", [Returns_Processed_Qty],
"Credit_Value_RT", [Credit_Value_RT],
"Returns_Qty_Delta", [Returns_Qty_Delta],
"Customer_Credit_Value_Delta", [Customer_Credit_Value_Delta],
"v_InventoryValue_RT", [v_InventoryValue_RT],
"Inventory_Value_Delta", [Inventory_Value_Delta],
"MinReturn_Status", [MinReturn_Status]
)

VAR __DS0Core =
FILTER(
KEEPFILTERS(__DS0CoreNoInstanceFilters),
OR(
OR(
OR([IsGrandTotalRowTotal], AND(NOT([IsGrandTotalRowTotal]), [IsDM1Total])),
AND(
AND(NOT([IsDM1Total]), [IsDM3Total]),
'Customer Name'[Business Name Major] IN {"AutoZone"}
)
),
AND(
AND(NOT([IsDM3Total]), 'Customer Name'[Business Name Major] IN {"AutoZone"}),
('Customer Name'[Business Name Major], 'Customer Name'[Customer Name_ MT]) IN {("AutoZone", "AUTOZONE VDP")}
)
)
)

VAR __DS0CoreTableByDM1 =
SELECTCOLUMNS(
KEEPFILTERS(
FILTER(KEEPFILTERS(__DS0Core), AND([IsDM1Total] = TRUE, [IsDM3Total] = TRUE))
),
"IsGrandTotalRowTotal", [IsGrandTotalRowTotal],
"'Customer Name'[Business Name Major]", 'Customer Name'[Business Name Major],
"SortBy_DM1_0", [v_InventoryValue_RM]
)

VAR __DS0CoreTableByDM3 =
SELECTCOLUMNS(
KEEPFILTERS(FILTER(KEEPFILTERS(__DS0Core), [IsDM3Total] = TRUE)),
"IsGrandTotalRowTotal", [IsGrandTotalRowTotal],
"'Customer Name'[Business Name Major]", 'Customer Name'[Business Name Major],
"IsDM1Total", [IsDM1Total],
"'Customer Name'[Customer Name_ MT]", 'Customer Name'[Customer Name_ MT],
"SortBy_DM3_0", [v_InventoryValue_RM]
)

VAR __DS0PrimaryWithSortColumns =
NATURALLEFTOUTERJOIN(
NATURALLEFTOUTERJOIN(
__DS0Core,
__DS0CoreTableByDM1
),
__DS0CoreTableByDM3
)

VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0PrimaryWithSortColumns,
[IsGrandTotalRowTotal],
0,
[SortBy_DM1_0],
0,
'Customer Name'[Business Name Major],
1,
[IsDM1Total],
0,
[SortBy_DM3_0],
0,
'Customer Name'[Customer Name_ MT],
1,
[IsDM3Total],
0,
[v_InventoryValue_RM],
0,
'Parts'[Catalog #],
1
)

VAR __DS0CoreNoInstanceFiltersNoTotals =
FILTER(
KEEPFILTERS(__DS0CoreNoInstanceFilters),
AND(AND([IsGrandTotalRowTotal] = FALSE, [IsDM1Total] = FALSE), [IsDM3Total] = FALSE)
)

EVALUATE
GROUPBY(
__DS0CoreNoInstanceFiltersNoTotals,
"MinRM_Qty", MINX(CURRENTGROUP(), [RM_Qty]),
"MaxRM_Qty", MAXX(CURRENTGROUP(), [RM_Qty]),
"Minv_InventoryValue_RM", MINX(CURRENTGROUP(), [v_InventoryValue_RM]),
"Maxv_InventoryValue_RM", MAXX(CURRENTGROUP(), [v_InventoryValue_RM]),
"MinCredit_Value_RM", MINX(CURRENTGROUP(), [Credit_Value_RM]),
"MaxCredit_Value_RM", MAXX(CURRENTGROUP(), [Credit_Value_RM]),
"Minv_Scrap", MINX(CURRENTGROUP(), [v_Scrap]),
"Maxv_Scrap", MAXX(CURRENTGROUP(), [v_Scrap]),
"MinReturns_Processed_Qty", MINX(CURRENTGROUP(), [Returns_Processed_Qty]),
"MaxReturns_Processed_Qty", MAXX(CURRENTGROUP(), [Returns_Processed_Qty]),
"MinCredit_Value_RT", MINX(CURRENTGROUP(), [Credit_Value_RT]),
"MaxCredit_Value_RT", MAXX(CURRENTGROUP(), [Credit_Value_RT]),
"MinReturns_Qty_Delta", MINX(CURRENTGROUP(), [Returns_Qty_Delta]),
"MaxReturns_Qty_Delta", MAXX(CURRENTGROUP(), [Returns_Qty_Delta]),
"MinCustomer_Credit_Value_Delta", MINX(CURRENTGROUP(), [Customer_Credit_Value_Delta]),
"MaxCustomer_Credit_Value_Delta", MAXX(CURRENTGROUP(), [Customer_Credit_Value_Delta]),
"Minv_InventoryValue_RT", MINX(CURRENTGROUP(), [v_InventoryValue_RT]),
"Maxv_InventoryValue_RT", MAXX(CURRENTGROUP(), [v_InventoryValue_RT]),
"MinInventory_Value_Delta", MINX(CURRENTGROUP(), [Inventory_Value_Delta]),
"MaxInventory_Value_Delta", MAXX(CURRENTGROUP(), [Inventory_Value_Delta])
)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
[IsGrandTotalRowTotal] DESC,
[SortBy_DM1_0] DESC,
'Customer Name'[Business Name Major],
[IsDM1Total] DESC,
[SortBy_DM3_0] DESC,
'Customer Name'[Customer Name_ MT],
[IsDM3Total] DESC,
[v_InventoryValue_RM] DESC,
'Parts'[Catalog #]


// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS(
{"'Customer Name'[Business Name Major]",
"'Customer Name'[Customer Name_ MT]",
"'Parts'[Catalog #]"},
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories Fields]
)

VAR __DS0Core =
CALCULATETABLE(
SUMMARIZE(
'2. Returns-Open: Catagories',
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories Fields],
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories Order],
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories]
),
KEEPFILTERS(__DS0FilterTable)
)

VAR __DS0BodyLimited =
TOPN(
152,
__DS0Core,
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories Order],
1,
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories Fields],
1,
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories],
1
)

EVALUATE
__DS0BodyLimited

ORDER BY
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories Order],
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories Fields],
'2. Returns-Open: Catagories'[2. Returns-Open: Catagories]

otravers
Community Champion
Community Champion

Interesting, I never noticed that though I don't use drill down often. You can use the performance analyzer functionality in Power BI Desktop to see whether the generated DAX queries are any different.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.