Error with VALUES Function When No Filter is Applied and Issue with ADDCOLUMNS in DAX Measure
I'm trying to create a DAX measure that calculates reliability, but I’m encountering an issue. The goal is to make the measure work when one or more equipment items are selected. However, when no equipment filter is applied, it throws an error in the graph. I need help adapting the formula to handle this case properly. Here’s the original code: Confiabilidade_Sistema = VAR t = SELECTEDVALUE(Tabela_TTF[TTF]) -- Time to failure (in hours) VAR Equipamentos = VALUES(TTF[Equipamento Corrigido]) VAR Confiabilidades = ADDCOLUMNS( Equipamentos, "Confiabilidade", VAR Beta = CALCULATE([b_eq], TTF[Equipamento Corrigido] = EARLIER(TTF[Equipamento Corrigido])) VAR Eta = CALCULATE(EXP(-[a_eq] / Beta), TTF[Equipamento Corrigido] = EARLIER(TTF[Equipamento Corrigido])) RETURN IF(NOT(ISBLANK(Beta)) && NOT(ISBLANK(Eta)), EXP(-POWER(t / Eta, Beta)), 1) ) RETURN 1 - PRODUCTX(Confiabilidades, 1 - [Confiabilidade]) Now, I’m trying to adapt the formula to handle both cases — when equipment is filtered and when no filter is applied: Confiabilidade_Sistema = VAR t = SELECTEDVALUE(Tabela_TTF[TTF]) -- Time to failure (in hours) VAR Equipamentos = IF( ISFILTERED(TTF[Equipamento Corrigido]), VALUES(TTF[Equipamento Corrigido]), FILTER( VALUES(TTF[Equipamento Corrigido]), NOT(ISBLANK(TTF[a_eq])) ) ) VAR Confiabilidades = ADDCOLUMNS( Equipamentos, "Confiabilidade", VAR Beta = CALCULATE([b_eq], TTF[Equipamento Corrigido] = EARLIER(TTF[Equipamento Corrigido])) VAR Eta = CALCULATE(EXP(-[a_eq] / Beta), TTF[Equipamento Corrigido] = EARLIER(TTF[Equipamento Corrigido])) RETURN IF(NOT(ISBLANK(Beta)) && NOT(ISBLANK(Eta)), EXP(-POWER(t / Eta, Beta)), 1) ) RETURN 1 - PRODUCTX(Confiabilidades, 1 - [Confiabilidade]) -- Calculates system reliability However, it returns the following error: "The ADDCOLUMNS function expected a table expression for the '' argument, but a string or numeric expression was used." The issue arises when no equipment is selected. If any equipment is selected, the graph works fine. Could you help me fix this issue?
Moda (medida de tendência central, valor que mais se repete) de uma coluna virtual.
Olá, Preciso calcular a moda (medida de tendência central da estatística, valor que mais se repete) de uma coluna virtual de quantidade de clinetes por semana. Minha coluna é virtual, e para calcular qual é o valor que mais se repete dessa coluna preciso fazer uma outra tabela virtual para contar quantas vezes cada valor se repete. mostrar o maior valor. Fiz, porém deu erro. Segue o print. Alguém consegue me ajudar com essa medida?
ADDCOLUMNS and SUMMARIZE - incorrect Total (Live Connection)
Please see below issue: As you can see, for the No of Sessions, the total should be 9, but i'm getting a total of 32 insead which is super weird. Since we're using a Live Connection, I don't have access to calculated columns. See below DAX I used: No of Sessions = VAR SessionsTable = ADDCOLUMNS ( SUMMARIZE ( 'Location-Practice-Rendering', 'Location-Practice-Rendering'[LeafName] ), "Total Encounters", [Encounters], "No of Days Total", [No of Days], "No of Sessions Per Day", IF ( [Encounters] <= 2, 0, IF ( [Encounters] > 10, 2, 1 ) ) ) RETURN SUMX( SessionsTable, [No of Days Total] * [No of Sessions Per Day]) Where [Encounters] and [No of Days] are measures: Encounters = CALCULATE ( [Amount], FILTER ( ALL ( 'Profitability Account' ), 'Profitability Account'[Description] = "Encounters" )) No of Days = CALCULATE ( DISTINCTCOUNT ( 'Time'[CalendarDate]), FILTER ( 'Time', 'Time'[DayOfWeekdayDescription] <> "Sunday" && 'Time'[DayOfWeekdayDescription] <> "Saturday" ) ) We created the following DAX measure which gives us the correct results, BUT it takes hours to load, where as my DAX above takes seconds. The following DAX gives us correct results: No Of Sessions Day = VAR _encounters = CALCULATE ( DISTINCTCOUNT ( 'Transaction Attributes'[Encounter Number__EBM__Encounter Number] ), FILTER ( 'Transaction Attributes', 'Base Measures'[Encounters] > 0 ) ) RETURN SWITCH ( TRUE (), _encounters <= 2, 0, _encounters >= 2 && _encounters <= 10, 1, _encounters > 10, 2 ) Total No of Sessions = VAR SessionsTable = SUMMARIZE ( 'Fact', 'Fact'[CustomerID], 'Fact'[CalendarDate], "No of Days Total", CALCULATE ( DISTINCTCOUNT ( 'Fact'[CalendarDate] ) ), "No of Sessions Per Day", [No Of Sessions Day] ) RETURN SUMX ( SessionsTable, [No of Days Total] * [No of Sessions Per Day] ) Here are the results from the above DAX: Can you please help me amened my original DAX so I can get the correct results? The ADDCOLUMNS SUMMARIZE works a lot faster. Unless we can fix the second DAX to make it faster? amitchandak - you have any thoughts on this issue? Any help would be much appreciated 🙂
Sum for last days of month per each category - how to simplify
Hello, I have a quite peculiar problem. I have a column with values that represents the state of Inventory for each Site (category). Which means that the most recent one value for each month is always last day per each site per month. Example for site 667 for november its going to be value 5 252 235.74 (31/12/2021) but for site 200 its going to be 79 967 894.18 (30/12/2021) The sum of those values should be 85 220 129.92 which is state of inventory for those two sites per december. I was able to calculate this with this measure: Inventory Cost = VAR _pretable = ADDCOLUMNS ( SUMMARIZE ( v_factinventorytransactions, v_dimdate[DateId], v_factinventorytransactions[SiteId] ), "InventoryCost", CALCULATE ( AVERAGE ( v_factinventorytransactions[RunningCost] ) ) ) VAR _table = FILTER ( _pretable, VAR _MaxDate = CALCULATE ( MAX ( v_factinventorytransactions[InventoryTransactionDateId] ), ALLSELECTED ( v_dimdate[DateId] ) ) RETURN v_dimdate[DateId] = _MaxDate ) RETURN SUMX ( _table, [InventoryCost] ) Which works perfectly but I'm wondering if it can be simplyfied. I want it to simplify, because when I want to use this measure inside another one that sums those Inventory Cost values per month for last 3 months and I have wrong answers. Which means that this Inventory Cost measure works but if I call out this measure in the one below it shows wrong numbers (but other measures, more simply ones work). Rolling3Months = VAR _EndDate = MAX(v_dimdate[Date]) VAR _Dates = DATESINPERIOD(v_dimdate[Date], _EndDate, -3, MONTH) VAR _Cost = [Inventory Cost] VAR _Inventory = SUMX(_Dates, CALCULATE(_Cost, ALL(v_dimdate[YearMonth]))) RETURN _Inventory I'm a little bit stuck and would be super appreciated when someone would pointed out my mistakes/errors here. I'm also providing sample power BI file with those. https://we.tl/t-eQSOYHm1ft Thank youSUMMARIZECOLUMNS Alternative
Hello all, In my data model, besides the fact table, there is a target table, which contains one target value per date and dimension. Facts: Targets: The target value should be weighted at denominators of the fact table. I have tried different approaches to realize this. The only one that works so far is to add calculated columns in the fact table, which can then be divided in the measure. For each day and dimension, I calculate the associated target value, which I multiply by the denominators: Target Average = var relevantRows = FILTER( Targets, Targets[Date] = 'Facts'[Date] && Targets[Dimension] = 'Facts'[Dimension] ) var foo = SELECTCOLUMNS(relevantRows, "Target", [Target]) return foo Target Absolut = Facts[Denominator] * Facts[Target Average] Weight = IF(NOT(ISBLANK(Facts[Target Average])), Facts[Denominator]) The result is as expected and correct regardless of the choice of dimension: I am very interested in a Measure-based solution. With SUMMARIZECOLUMNS I could display the correct value. However, this function has some limitations so it stops working as soon as the dimension or date is brought into the context/visual. (the second image shows what happens when I activate the SUMMARIZECOLUMNS measure in the first visual. The error message appears that SUMMARIZECOLUMNS cannot be used in this context). Target via Measure SUMMARIZECOLUMNS = VAR _table = ADDCOLUMNS ( SUMMARIZECOLUMNS ( 'Date'[Date], 'Dimension'[Dimension], "_weight", SUM ( 'Facts'[Denominator] ), "_target", MAX ( 'Targets'[Target] ) ), "_target absolut", [_weight] * [_target] ) VAR _tableFiltered = FILTER ( _table, NOT ( ISBLANK ( [_target absolut] ) ) ) RETURN DIVIDE ( SUMX ( _tableFiltered, [_target absolut] ), SUMX ( _tableFiltered, [_weight] ), BLANK () ) As an alternative for this I have worked with SUMMARZIZE and ADDCOLUMNS, but fail to get the dimension and date into a virtual table. This results in an incorrect value as long as the dimension is not selected: Target via Measure SUMMARIZE = var _table1 = SUMMARIZE( 'Date', 'Date'[Date], "_weight", SUMX(RELATEDTABLE('Facts'), 'Facts'[Denominator]), "_target", MAXX(RELATEDTABLE('Targets'), 'Targets'[Target]) ) var _table2 = ADDCOLUMNS(_table1, "_targetAbsolut", [_weight] * [_target] ) RETURN DIVIDE(SUMX(_table2, [_targetAbsolut]), SUMX(_table2, [_weight]), BLANK()) I know that in the virtually created table, the dimension information is missing. But I have no idea how to implement this. The requirement is to get the result from [Target via Columns] or [Target via Measure SUMMARIZECOLUMNS] without any additional calculated columns and not make any change in the relationships. Does anyone have any idea about this? Thanks in advance and kind regards Chris
