Forum Discussion

Frequent Visitor

2 years ago

Cube.CollapseAndRemoveColumns slow after List.Buffer

Using List.Buffer for filtering in a column improved performance, but applying collapse (column) caused a significant slowdown in the query. Any help to this problem? Thanks in advance.

Anonymous

Not applicable

2 years ago

How do you apply collapse (column)? Can you provide the M code you used?

Best Regards,
Jing

Frequent Visitor

2 years ago

Hi, here is the code:

#"Vendedor BAANV" = Cube.Transform(#"Filtro Supervisor",
{
{Cube.AddAndExpandDimensionColumn, "[Piramide Ventas]", {"[Piramide Ventas].[Piramide Ventas].[Vendedor BAANV]"}, {"Vendedor BAANV"}}
}),
Cliente = Cube.Transform(#"Vendedor BAANV",
{
{Cube.AddAndExpandDimensionColumn, "[Cliente]", {"[Cliente].[Por Cliente].[Cliente]"}, {"Cliente"}}
}),
Medidas = Cube.Transform(Cliente,
{
{Cube.AddMeasureColumn, "No. Clientes Efectivos", "[Measures].[No. Clientes Efectivos]"},
{Cube.AddMeasureColumn, "No. Clientes Ruta", "[Measures].[No. Clientes Ruta]"}
}),
ListQuery = List.Buffer(Lista),
#"Filas filtradas" = Table.SelectRows(Medidas, each (List.Contains(ListQuery,[Cliente])<>true )),
#"Columnas contraídas y quitadas" = Cube.CollapseAndRemoveColumns(#"Filas filtradas", {"Cliente"})
in
#"Columnas contraídas y quitadas"

Thanks