Forum Discussion

ClaudioF's avatar
ClaudioF
Icon for Helper II rankHelper II
1 year ago
Solved

combinatorics in dinamic dax

Hello guys, i have posted here so many times about getting a logic right, but i just noticed it was impossible, in the way i was thinking, no one could help me. I have a new aproach of how to get th...
advanced dax
data engineering
DAX
Help
matrix
SUMMARIZE
  • ClaudioF's avatar
    ClaudioF
    1 year ago

    didnt work.. i think its not even possible though, i am thinking about changing to python completlty in order to achieve this result i need

VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
1 year ago

Hi ClaudioF 

 

Try this:

BestCombination =
VAR CurrentProduct = SELECTEDVALUE('DB_ORDERS'[Produto])

VAR OrdersTable =
    FILTER(
        ALL('DB_ORDERS'),
        'DB_ORDERS'[Produto] = CurrentProduct
    )

VAR AllCombos =
    GENERATE(
        OrdersTable,
        FILTER(
            OrdersTable,
            'DB_ORDERS'[Pedido] <= EARLIER('DB_ORDERS'[Pedido])
        )
    )

VAR CombosWithSum =
    ADDCOLUMNS(
        AllCombos,
        "PedidosConcatenados", CONCATENATEX(
            FILTER(
                OrdersTable,
                'DB_ORDERS'[Pedido] <= EARLIER('DB_ORDERS'[Pedido])
            ),
            'DB_ORDERS'[Pedido],
            ", ",
            'DB_ORDERS'[Pedido], ASC
        ),
        "Soma_Quant_Falta", SUMX(
            FILTER(
                OrdersTable,
                'DB_ORDERS'[Pedido] <= EARLIER('DB_ORDERS'[Pedido])
            ),
            'DB_ORDERS'[QT. falt]
        ),
        "Qtd_Pedidos", COUNTROWS(
            FILTER(
                OrdersTable,
                'DB_ORDERS'[Pedido] <= EARLIER('DB_ORDERS'[Pedido])
            )
        )
    )

VAR FilteredCombos =
    FILTER(
        CombosWithSum,
        [Soma_Quant_Falta] <= 21
    )

VAR MaxQtdPedidos =
    MAXX(FilteredCombos, [Qtd_Pedidos])

VAR BestComboFinal =
    TOPN(
        1,
        FILTER(FilteredCombos, [Qtd_Pedidos] = MaxQtdPedidos),
        [Soma_Quant_Falta], DESC
    )

RETURN
    CONCATENATEX(
        BestComboFinal,
        [PedidosConcatenados],
        "; "
    )

 

Generate combinations by comparing each order to orders with equal or lower numbers to simplify calculations.
Calculate the sum (Soma_Quant_Falta) and count (Qtd_Pedidos) for each combination.
Filter only combinations whose sum is <= 21.
Pick the combination with the maximum number of orders (Qtd_Pedidos).
Finally, concatenate the order numbers clearly for display.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

 

LinkedIn|Twitter|Blog |YouTube 

  • ClaudioF's avatar
    ClaudioF
    Icon for Helper II rankHelper II
    1 year ago

    Hey there! thankyou for your return;

    This code you provided is generating an error: The GENERATE function does not allow two columns with the same name "DB_ORDERS"[Cliente]. i got this same problem in other attempts..

    • VahidDM's avatar
      VahidDM
      Icon for Super User rankSuper User
      1 year ago

      You right 🙂
      Try this:

      BestCombination =
VAR CurrentProduct = SELECTEDVALUE('DB_ORDERS'[Produto])

VAR OrdersTable =
    FILTER(
        ALL('DB_ORDERS'),
        'DB_ORDERS'[Produto] = CurrentProduct
    )

VAR OrdersDistinct =
    SELECTCOLUMNS(
        OrdersTable,
        "Pedido", 'DB_ORDERS'[Pedido],
        "QT_falt", 'DB_ORDERS'[QT. falt]
    )

VAR Combos =
    GENERATE(
        OrdersDistinct,
        FILTER(
            OrdersDistinct,
            [Pedido] <= EARLIER([Pedido])
        )
    )

VAR ComboSummary =
    ADDCOLUMNS(
        Combos,
        "PedidosConcatenados",
            CONCATENATEX(
                FILTER(OrdersDistinct, [Pedido] <= EARLIER([Pedido])),
                [Pedido],
                ", ",
                [Pedido], ASC
            ),
        "Soma_Quant_Falta",
            SUMX(
                FILTER(OrdersDistinct, [Pedido] <= EARLIER([Pedido])),
                [QT_falt]
            ),
        "Qtd_Pedidos",
            COUNTROWS(
                FILTER(OrdersDistinct, [Pedido] <= EARLIER([Pedido]))
            )
    )

VAR FilteredCombos =
    FILTER(
        ComboSummary,
        [Soma_Quant_Falta] <= 21
    )

VAR MaxQtdPedidos =
    MAXX(FilteredCombos, [Qtd_Pedidos])

VAR BestCombo =
    TOPN(
        1,
        FILTER(FilteredCombos, [Qtd_Pedidos] = MaxQtdPedidos),
        [Soma_Quant_Falta], DESC
    )

RETURN
    CONCATENATEX(
        BestCombo,
        [PedidosConcatenados],
        "; "
    )
      • ClaudioF's avatar
        ClaudioF
        Icon for Helper II rankHelper II
        1 year ago

        didnt work.. i think its not even possible though, i am thinking about changing to python completlty in order to achieve this result i need