issue with SUMMARIZECOLUMNS in a measure
Good evening, I have yearly forecasts per items I want to allocate them at a week level, taking into account the working days and an allocation table per month. Each item belongs to a department (Dpt) : This department defines which percentages to use in order to allocate this yearly forecast at a month level : I have a Calendar table that defines if each date is a working day or not : the Data model : In DAX studio, I have been able to build the temporary table needed by the calculations, because some weeks may belonging to different months : EVALUATE VAR Table_Year_Month_Week = FILTER ( SUMMARIZECOLUMNS ( Calendar[Year], Calendar[Month number], Calendar[YYCW], Items[Item], "Year_fcst", CALCULATE ( SUM ( 'YearlyForecasts'[Yearly Quantity] ), TREATAS ( VALUES ( Calendar[Year] ), 'YearlyForecasts'[Year] ) ), "Month_pct", VAR pct = CALCULATE ( MIN ( 'AllocationPerMonthDpt'[pct] ), TREATAS ( VALUES ( Items[Dpt] ), 'AllocationPerMonthDpt'[Dpt] ), TREATAS ( VALUES ( Calendar[Year] ), 'AllocationPerMonthDpt'[Year] ), TREATAS ( VALUES ( Calendar[Month number] ), 'AllocationPerMonthDpt'[Month number] ) ) RETURN IF ( ISBLANK ( pct ), 0, pct ), "Month_WD", CALCULATE ( SUM ( Calendar[working day] ), FILTER ( ALL ( Calendar ), Calendar[Year] IN VALUES ( Calendar[Year] ) && Calendar[Month number] IN VALUES ( Calendar[Month number] ) ) ), "Week_WD", CALCULATE ( SUM ( Calendar[working day] ) ) ), Calendar[YYCW] = 2627 && Items[Item] = "ItemA" ) VAR tot = SUMX ( Table_Year_Month_Week, [Year_fcst] * [Month_pct] * [Week_WD] / [Month_WD] ) RETURN Table_Year_Month_Week //{tot} I have read in this article (https://www.sqlbi.com/articles/summarizecolumns-best-practices/ ) that “SUMMARIZECOLUMNS … in 2025 can be used in measures”. So do I : Weekly forecast = VAR Table_Year_Month_Week = SUMMARIZECOLUMNS ( Calendar[Year]; Calendar[Month number]; Calendar[YYCW]; Items[Item]; "Year_fcst"; CALCULATE ( SUM ( 'YearlyForecasts'[Yearly Quantity] ); TREATAS ( VALUES ( Calendar[Year] ); 'YearlyForecasts'[Year] ) ); "Month_pct"; VAR pct = CALCULATE ( MIN ( 'AllocationPerMonthDpt'[pct] ); TREATAS ( VALUES ( Items[Dpt] ); 'AllocationPerMonthDpt'[Dpt] ); TREATAS ( VALUES ( Calendar[Year] ); 'AllocationPerMonthDpt'[Year] ); TREATAS ( VALUES ( Calendar[Month number] ); 'AllocationPerMonthDpt'[Month number] ) ) RETURN IF ( ISBLANK ( pct ); 0; pct ); "Month_WD"; CALCULATE ( SUM ( Calendar[working day] ); FILTER ( ALL ( Calendar ); Calendar[Year] IN VALUES ( Calendar[Year] ) && Calendar[Month number] IN VALUES ( Calendar[Month number] ) ) ); "Week_WD"; CALCULATE ( SUM ( Calendar[working day] ) ) ) VAR tot = SUMX ( Table_Year_Month_Week; [Year_fcst] * [Month_pct] * [Week_WD] / [Month_WD] ) RETURN // Table_Year_Month_Week tot But my measure don’t work. I get an error message : "MdxScript(Model) (7, 9) Calculation error in measure 'YearlyForecasts'[Weekly forecast]: SummarizeColumns() and AddMissingItems() may not be used in this context." link to the excel file ForecastAllocation.xlsx How to modify my measure to avoid the error?
SUMMARIZECOLUMNS and ORDER BY
Hi, I'm trying to add ORDER BY in the following DAX measure, but how ? I can not get it working. My goal is to organize return so that the largest Poisson value would be the first Goals[NumOfGoals] is table with goals 0-6 HomeTeamAttack is the expected goal value for the team At first I used the SUMMARIZE function, but it won't work with the ORDER BY ..... CalculatePoissonCombined = VAR _table4 = SUMMARIZECOLUMNS ( Goals[NumOfGoals], "Poisson", SUMX ( VALUES ( Goals[NumOfGoals] ), POISSON.DIST ( Goals[NumOfGoals], [HomeTeamAttack], FALSE() ) * 100 ) ) RETURN SUMX ( _table4, [Poisson] )
DAX Summarizecolumns and then countrows per group
Hi, I have query with 3 dimensions (Continent/CountryRegion/City) part of paginated report. I would like to highlight only those child groups where there is 1 item. So I need to get a count of items per each parent group. In this case, let's write the query, to be more exact. EVALUATE VAR __t1 = SUMMARIZECOLUMNS ( Customer[Continent], Customer[CountryRegion], Customer[City], "Amount", [Sales Amount] ) RETURN GROUPBY( __t1, Customer[Continent], Customer[CountryRegion], "Count" , countX(CURRENTGROUP(), Customer[CountryRegion] ) ) ORDER BY Customer[Continent] DESC Which brings me this Result I am looking for is And in a case where I have 3 dimensions EVALUATE VAR __t1 = SUMMARIZECOLUMNS ( Customer[Continent], Customer[CountryRegion], Customer[City], "Amount", [Sales Amount] ) RETURN GROUPBY( __t1, Customer[Continent], Customer[CountryRegion], Customer[City], "Count" , countX(CURRENTGROUP(), Customer[City] ) ) ORDER BY Customer[Continent] DESC I get But I would like to see How should I approach this? Thank you!SWITCH over SUMMARIZECOLUMNS based on parameter value
Hello, I have created parameter and based on that I would like to calculate dynamic table using SUMMARIZECOLUMNS function. Is that possible at all? Below you can find my DAX idea using SWITCH and IF however it does not work. I need a table as a result of SWITCH / IF statement. EVALUATE SWITCH ( TRUE (), @Selected_Values = 0, SUMMARIZECOLUMNS ( 'Test Table'[Column] ), SUMMARIZECOLUMNS ( 'Test Table'[Column], KEEPFILTERS ( FILTER ( ALL ( 'Sources'[Source] ), PATHCONTAINS ( @Source, 'Sources'[Source] ) ) ), "Calculation", COUNTROWS ( 'Test Table' ) ) ) And with IF: EVALUATE IF ( @Selected_Values = 0, SUMMARIZECOLUMNS ( 'Test Table'[Column] ), SUMMARIZECOLUMNS ( 'Test Table'[Column], KEEPFILTERS ( FILTER ( ALL ( 'Sources'[Source] ), PATHCONTAINS ( @Source, 'Sources'[Source] ) ) ), "Calculation", COUNTROWS ( 'Test Table' ) ) ) ThanksWhat's the difference between these 2 queries?
A small data model with two tables <Dfact> <Ddim> <Ddim>[SubCategory] (1) --- (*) <Dfact>[SubCategory] The first query returns 3. Whereas the second query returns 1. <Dfact> ID SubCategory 1 SC1 2 SC1 3 SC2 4 SC2 5 SC3 6 SC3 <Ddim> Category SubCategory C1 SC1 C1 SC2 C2 SC3 C2 SC4 C3 SC5 EVALUATE CALCULATETABLE ( { COUNTROWS ( Ddim ) }, Dfact, Dfact[SubCategory] = "SC1" ) EVALUATE SUMMARIZECOLUMNS ( Dfact, FILTER ( ALL ( Dfact[SubCategory] ), Dfact[SubCategory] = "SC1" ), "count", COUNTROWS ( Ddim ) ) What's the difference between the 2 quries? Thanks in advance.
DAX for selecting all of the columns and data within a table
Hi all, I am hoping someone can help? I have a bit of a DAX problem (hey who doesn’t)… I have quite a complicated Power Automate Flow that takes the output from a PBI query, saves it into sharepoint by splitting and joining multiple files, which allows me to automate an extract of 100,000+ rows into CSV. My flow works fine, except for the DAX within the ‘Run a query against a dataset’ flow. All I want to do is export the contents of the table ‘Help’. The DAX in my examples work on columns that contain numbers, but it appears to fail if I add a column that contains a String like I have in 'Help’ [Test4], 'Help’ [Test5] 'Help’ [Test6]. If i add 'Help’ [Test3] (which is numeric) to the code below it will work. I would like to use DAX to select all the columns in my table (about 35+) // DAX Query DEFINE VAR __DS0FilterTable = // Removes repeat headers FILTER(KEEPFILTERS(VALUES('Help’[Index])), and('Help’ [Index] >= @{variables('MinRows')}, 'Help’ [Index] <= @{variables('IncrRows')})) // Export content of table VAR __DS0Core = CALCULATETABLE( SUMMARIZECOLUMNS( 'Help’[File.Name], 'Help’ [Index], 'Help’ [Test1], 'Help’ [Test2]), __DS0FilterTable ) EVALUATE __DS0Core ORDER BY ‘Help’[Index] Eternally grateful for any help!When creating a new table using Summarizecolumns, can I use a parameter?
Hi Guys, I've created a new table based on an existing one using the following DAX: New Table = SUMMARIZECOLUMNS ( 'Original - wind cap factor'[Hourly Wind Max Gust 10m (km/h)], 'Original - wind cap factor'[Year], FILTER ( 'Original - wind cap factor', 'Original - wind cap factor'[Hourly Wind Max Gust 10m (km/h)] > 80)) This new table has 7 rows only - which is correct for what I need. However, instead of having the fixed value in blue 80, I would like to use a parameter (numeric field) I've created in the visuals - so that the user could change the parameter as he needs and the numbers get recalculated based on that. This parameter is created under the Modelling tab - New pArameter - Numeric Field. The problem is that when I have it as fixed value 80 it works, but when I use the parameter, it does not work. Below is the DAX I tried to use linking to the parameter: New Table = SUMMARIZECOLUMNS ( 'Original - wind cap factor'[Hourly Wind Max Gust 10m (km/h)], 'Original - wind cap factor'[Year], FILTER ( 'Original - wind cap factor', 'Original - wind cap factor'[Hourly Wind Max Gust 10m (km/h)] > SELECTEDVALUE ( '.Wind speed'[Wind speed] ) ) ) When I use the above DAX linking to the parameter, the amount row of grows as it does not summarize the columns as I wanted. Would you guys be able to help me make it work? Below is a photo of how this parameter is shown for the users: Thanks, DiegoMeasure for a very complex 2 steps summarize case
I'm trying to translate into a measure for my main table a very specific calculation that I've been able to achieve step-by-step on a summarized table, adding calculated columns in cascade. The measure calculates the amounts to be eliminated between related parties that belong to the same Business Line (BL). The amount to be eliminated is the minimum between the sum of the costs and the sum of the revenues that matches a 3 elements key designed for that purpose (Elimination key = "BL-Company-Counterpart"). The attached summarized table shows the desired result, the challenge is to de-summarize that result to apply it on the main table using a measure, for consolidation purposes. Since the case is quite complex to be shown in a post, I opted to clarify the desired result on a XLS sheet and a test model. Download Example Model I truly appreciate the help of the community. Thank everybody for your support. Marco, Monterrey, Mexico
