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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
ClaudioF
Helper II
Helper II

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 the desired result, its by using combinatorics, of course, i need to know if its even possible in the dax.

I have the image bellow that shows how the combinations might work, and ahead i also have the code i'm trying, its not working properly and i would like some help with it.

Captura de tela 2025-03-19 154453.png

 

In this code, the objective is have at the end the combinations that attend to the condition that the amount of concatenated orders is the highest and the sum of values are <= 21.

 

Obs. this code might work with many different products, but to this example i just used one.

Code:

 

FinalCorrectStatus =
VAR CurrentItem = SELECTEDVALUE('DB_ORDERS'[Produto])
VAR OrdersTable = FILTER(ALL('DB_ORDERS'), 'DB_ORDERS'[Produto] = CurrentItem)

VAR OrderCombinations =
ADDCOLUMNS(
GENERATEALL(
OrdersTable,
SUMMARIZE(
OrdersTable,
'DB_ORDERS'[Pedido],
"Soma_Quant_Falta", SUM('DB_ORDERS'[QT. falt])
)
),
"PedidosConcatenados", CONCATENATEX(OrdersTable, 'DB_ORDERS'[Pedido], ", "),
"Qtd_Pedidos", COUNTROWS(OrdersTable) -- Conta o número de pedidos na combinação
)

VAR FilteredCombinations =
FILTER(OrderCombinations, [Soma_Quant_Falta] <= 21)

VAR MaxPedidos = MAXX(FilteredCombinations, [Qtd_Pedidos]) -- Encontra o maior número de pedidos

VAR BestCombination =
FILTER(FilteredCombinations, [Qtd_Pedidos] = MaxPedidos) -- Mantém apenas a combinação com mais pedidos

RETURN BestCombination

 

Can anyone please help me with it?

1 ACCEPTED SOLUTION

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

View solution in original post

5 REPLIES 5
VahidDM
Super User
Super User

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 

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..

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],
        "; "
    )

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

Hi @ClaudioF ,

 

Thank you for reaching out to Microsoft Fabric Community. Sorry for the delay in response.

 

Thank you @VahidDM  for the prompt response.

 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

 

Thank you!!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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