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 🙂RANKX with SUMMARIZECOLUMNS and Filter
Hi all, Here is a sample of my dataset, where: - Job: one job can have multiple tasks, and be executed more than once in a day (job timestamp repeated). - Task: multiple tasks happen within a job. - Job TimeStamp: date and time the job started. One job can have multiple executions within a day. - Job Date: date the job started. - Job Status: can be either G or C. - Unique Key: concatenation between Job and Job TimeStamp. Here is a table I constructed using the following measures: G = CALCULATE(DISTINCTCOUNTNOBLANK(Table1[Unique Key]),Table1[Job Status]="G") Distinct Count Unique Key = DISTINCTCOUNTNOBLANK(Table1[Unique Key]) %G = IF([G]>0,[G]/[Distinct Count Unique Key],0) Average = CALCULATE([Distinct Count Unique Key], REMOVEFILTERS(Table1[Job]))/CALCULATE(DISTINCTCOUNTNOBLANK(Table1[Job]),REMOVEFILTERS(Table1[Job])) What I want to do a ranking of those jobs that are above average and below average. The jobs that are above average (Distinct Count Unique Key > Average), I want to create a table that contains the following: On the other hand, for those below average (Distinct Count Unique Key < Average): This ranking should be filtered with a slicer by Job Date. Ranking = VAR SummaryTable = FILTER( SUMMARIZECOLUMNS(Table1[Job],Table1[Job Date], "job_executions",DISTINCTCOUNTNOBLANK(Table1[Unique Key]), "job_failed_aborted",CALCULATE(DISTINCTCOUNTNOBLANK(Table1[Unique Key]),Table1[Job Status]="G"), "pcte_job_failed_aborted",CALCULATE(DISTINCTCOUNTNOBLANK(Table1[Unique Key]),Table1[Job Status]="G")/DISTINCTCOUNTNOBLANK(Table1[Unique Key])), [job_failed_aborted]>0) RETURN RANKX(ALL(SummaryTable[job_name]),[pcte_job_failed_aborted],,DESC) But it is not working. Could you please help me out? Thanks a lot! Best, S
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 you
