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. 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?
logical error in dynamic filtering for calculated tables using summarize
Hi All I have created a measure using summarize which dynamically calculates a list for last year and this works fine for all the filters. Still, i am unable to filter it dynamically using the column u have created the filter for(Append1[CustomerList_Filtered]). if i insert Append1[CustomerList_Filtered] in the Allexcept condition i am getting an error "The column 'CustomerList_Filtered' specified in the 'SUMMARIZE' function was not found in the input table." VAR My_table = SUMMARIZE ( FILTER ( ALLEXCEPT( Append1 , Append1[Service Centre Name], Append1[Channel V1] , Append1[NSO] , Append1[CustomerOrganisation], Append1[Month No] ) , Append1[StatusDateTime] >= start_Date_LY && Append1[StatusDateTime] <= End_Date_LY ) , Append1[CustomerList_Filtered], "Accepted Jobs2", [Accepted Jobs.AG], "Accepted Revenue2", [Accepted Sales.AG] )
DAX equivalent of GROUP BY and MIN - How do I count products launched, by year?
I want to create a measure that counts how many products were sold for the first time in a given year. That means if the product was sold in any year prior, it should not count in the current year. I would expect to do something like this: (code can be run and iterated here: [https://dax.do/fDTAmTl3kDIMaF/][1] ) DEFINE MEASURE 'Sales'[m1] = CALCULATE ( DISTINCTCOUNT (Sales[ProductKey]), SUMMARIZECOLUMNS ( Sales[ProductKey], CALCULATETABLE ( Sales, ALL( Sales[Order Date] ), ALL( 'Date'[Date] ), ALL( 'Date'[Calendar Year] ) ), "earliest_sale", MIN( Sales[Order Date] ) ), ALL( Sales[Order Date] ), ALL( 'Date'[Date] ) ) EVALUATE SUMMARIZECOLUMNS ( 'Date'[Calendar Year], "number of products sold for the first time", 'Sales'[m1] ) However, this returns the following: Which is exactly the same result as the one I get from EVALUATE SUMMARIZECOLUMNS ( 'Date'[Calendar Year], 'Sales', "distinct product sales", DISTINCTCOUNT ( Sales[ProductKey] ), "total sales", COUNTROWS ( 'Sales' ) ) Finally, going perhaps, a little bit crazy, I tried this: EVALUATE SUMMARIZECOLUMNS ( 'Date'[Calendar Year], FILTER( 'Sales', Sales[Order Date] = CALCULATE( MIN( Sales[Order Date] ), SUMMARIZE ( CALCULATETABLE ( 'Sales', ALL ( Sales[Delivery Date] ), ALL ( 'Date'[Date] ), ALL ( 'Date'[Calendar Year] ) ), Sales[ProductKey] ), ALL ( Sales[Delivery Date] ), ALL ( 'Date'[Date] ), ALL ( 'Date'[Calendar Year] ) ) ), "distinct product sales", DISTINCTCOUNT ( Sales[ProductKey] ), "sales", COUNTROWS ( 'Sales' ) ) And got: Any help would be much appreciated. I wan to count the number of products in each year that were never sold before then, i.e. were sold in that year, for the first time ever. [1]: https://dax.do/fDTAmTl3kDIMaF/How to create index within a grouping in a matrix
Hi, I'm trying to create a measure that results in the Index column above. Assume that there is a matrix with a few layers in it, Region, SubRegion, Fiscal Year, and then Accounts. I would like the Index to start at the Fiscal Year Level. See above for reference. Repetition across the level below (Accounts) may or may not be needed. Thank you!Slicer doesn't work for table which is created in variable
Hi, I'm trying to create a new table for some specific date periods and percentage changes for some of them. I used calculated column for deposit and brought measures per row. It looks like as below, The issue is that, country- brand and brandsplit slicers don't affect the table. How can I adjust my table "_table1" as variable? The measure for yesterday, Thank you, VK
