"var"

8 Topics

Count new value as a single frequency, instead of twice, in the same DAX formula
I have a table (actions_taken) with columns called project_number, action_effective_dt, and action_name. All these values can and do repeat. Each project number can repeat & have multiple action names associated with it & multiple action effective dates. One of you helped me write the DAX formula below (thank you!), but I now want to modify this formula to count the “IE/FE” as a single occurrence. Currently the “IE/FE” is being double counted. DAX Formula: ClosingsAction = VAR CurrentProject = actions_taken[project_number] VAR CurrentDate = actions_taken[action_effective_dt] VAR HasA = CALCULATE( COUNTROWS(actions_taken), ALL(actions_taken), -- Overrides context transition to scan the whole table actions_taken[project_number] = CurrentProject, actions_taken[action_effective_dt] = CurrentDate, actions_taken[action_name] = " Initially Endorsed" ) > 0 VAR HasB = CALCULATE( COUNTROWS(actions_taken), ALL(actions_taken), -- Overrides context transition to scan the whole table actions_taken[project_number] = CurrentProject, actions_taken[action_effective_dt] = CurrentDate, actions_taken[action_name] = "Finally Endorsed" ) > 0 RETURN SWITCH( TRUE(), -- If both A and B exist on same date for the same project actions_taken[action_name] IN {"Initially Endorsed","Finally Endorsed"} && HasA && HasB, "IE/FE", -- Individual mappings actions_taken[action_name] = "Initially Endorsed", "IE", actions_taken[action_name] = "Finally Endorsed", "FE", actions_taken[action_name] = "Firm Application Withdrawn", "W", actions_taken[action_name] = "Firm Commitment Expired", "E", BLANK() ) Sample data: Project_number Action_effective_dt Action_name Ghj123 5/20/2026 Initially Endorsed Ghj123 5/20/2026 Finally Endorsed Hop963 4/8/2026 Firm Commitment Expired Lty789 3/7/2026 Firm Application Withdrawn Dsw493 3/7/2026 Firm Commitment Expired Kjh259 3/5/2026 Initially Endorsed Table outcomes with reclassified categories from “ClosingsAction” column: Action_Name Frequency IE/FE 2 (I want this to be counted as a single action/ occurrence) E 2 W 1 IE 1
Solved
Help_me
2 months ago Place DAX Commands and Tips
1.4KViews
0likes
8Comments
DAX - how to combine 3 working measures to create a clean final measure
Hi everyone, I have 3 separate DAX measures. They all work fine in its current state, but want to clean it up. The second measure relies on the first meaure, then the 3rd measure relies on the 2nd measure, just like a hierarchy. For the life of me, I am trying to combine them into one measure. I am having trouble with structuring them within one measure. Can someone please help me? Here are my 3 measures which all work perfectly in its current state: First Measure: --Sum total effort in hours Project Effort Estimate (Hrs) = sum(Effort_Estimate[HoursEffortPerDay]) Second Measure: --Convert Sum of total Hours to FTE Project Effort Estimate (FTE) = VAR NumberofDaysInDate = CALCULATE ( COUNTROWS ( FILTER ( Dim_Date, Dim_Date[IsWorkDay] = TRUE() ))) VAR FullTimeFTE = CALCULATE ( [Project Effort Estimate (Hrs)], FILTER (Effort_Estimate, Effort_Estimate[employmentType] = "FullTime" ) ) / NumberofDaysInDate / 7.5 VAR ContractorFTE = CALCULATE ( [Project Effort Estimate (Hrs)], FILTER (Effort_Estimate, Effort_Estimate[employmentType] = "Contractor" ) ) / NumberofDaysInDate / 8 RETURN FullTimeFTE + ContractorFTE Measure 3: --Summarize the FTE totals SUMX( SUMMARIZE( 'Effort_Estimate', Effort_Estimate[ID], "TotalFTE", [Project Effort Estimate FTE Total] ), [TotalFTE] )
Solved
Deevo_
1 year ago Place DAX Commands and Tips
925Views
0likes
4Comments
Variable para calcular comisiones de grupos de vendedores
Hola comunidad! Necesito crear una variable para calcular el incentivo de 3 grupos de vendedores. Paso información en las tablas. En caso de cumplir su objetivo de venta, cobran el INCENTIVO I. Si superan su objetivo de venta en un 20%, cobran el INCENTIVO II. Me dan una idea de cómo sacar este cálculo mediante la creación de variables? Muchas gracias!
GuilleLus
2 years ago Place DAX Commands and Tips
414Views
0likes
1Comment
Help to create a measure that calculates the upside of the diagonal
Hi all, I have two date info in my dataset and trying to full fill the upside diagonal of the table. Sample Dataset, Year MonthNumber MonthName Customerid Deposit_Year Deposit_Month 2023 6 June 2956 2023 6 2023 7 July 3257 2022 12 2023 10 October 3624 2023 10 2023 1 January 3258 2022 12 2023 11 November 3629 2023 11 2023 1 January 3261 2023 1 2023 2 February 3258 2022 12 2023 7 July 2551 2022 10 2023 11 November 1052 2023 2 2023 12 December 3609 2023 5 2023 12 December 3640 2023 12 2023 4 April 3241 2022 10 2023 6 June 3609 2023 6 2023 4 April 3268 2023 3 2023 12 December 3641 2023 12 2023 9 September 2858 2023 6 2023 1 January 1859 2022 10 2023 6 June 2689 2022 10 2023 11 November 3381 2023 5 2023 6 June 3304 2023 6 2023 7 July 3593 2023 6 2023 3 March 1779 2022 10 2023 9 September 3283 2023 8 2023 9 September 1799 2022 10 2023 5 May 3302 2023 5 2023 10 October 2836 2023 1 2023 1 January 7316 2023 1 2023 11 November 2961 2023 11 2023 5 May 2867 2022 10 2023 8 August 3591 2023 6 2023 8 August 3618 2023 8 2023 8 August 2634 2023 5 2023 7 July 1044 2022 10 2023 1 January 1647 2022 11 2023 5 May 3608 2023 5 2023 3 March 3261 2023 1 2023 4 April 3265 2023 2 I can calculate the diagonal values by this measure, Count Distinct Customer First Month of Year = VAR selected_year= SELECTEDVALUE(table1[Year]) VAR selected_month=SELECTEDVALUE(table1[MonthNumber]) RETURN CALCULATE( DISTINCTCOUNT(table1[CustomerID]), FILTER(table1, table1[Deposit_Year]=selected_year && table1[Deposit_Month]=selected_month) ) But, I need to assign these diagonal values to all next months of the year like below, - Row= table1(Deposit_Year) and table1(Deposit_Month) - Column= table1(Year) and table1(Month) Thank you, Veli
vkisa
2 years ago Place DAX Commands and Tips
524Views
0likes
2Comments
Doing a rank with a column created in variable
Hi everyone, I need to rank the Sales of the Employees, but I have a table and a new column called Test created as a variable in th DAX. This is to improve the performance. I have tried it without and it is taking too long to load, so this is my only solution. The loading time is no problem anymore but now I dont know how to iterate through the new column Test to get the ranking right. I need to use that column (which is a replication of the column Sales) because the column Sales is based on a lot of measures, which is the reason why it takes so long to load. The query is executing but the rankings come out all as 1. It must be due to the SUMX() that just compares every row with itself. How can I fix this? Your help is gretaly apreciated. Measure = VAR table_ = ADDCOLUMNS ( SUMMARIZE ( 'TableEmployee', 'TableEmployee'[Employee], "Sales", 'TableSales'[Sales] ), "Test", [Sales] ) RETURN RANKX( ALLSELECTED( 'TableEmployee'[Employee] ) , ( SUMX(table_, [Test])),,ASC,Dense)
Anonymous
2 years ago Place DAX Commands and Tips
1KViews
0likes
3Comments
Need Help with DAX 2-Day Lag Formula return different data sources based on future vs. past
Hi, I am trying to create a DAX formula that will return hours depending on the day. Please help! I am very stuck. For past dates before TODAY and YESTERDAY, I want to return the data from "Actual Hours" measure/column. For TODAY, YESTERDAY and FUTURE DAYS, I want to return the data from "Scheduled Hours" measure/column. I am currently using the below formula but it is not bringing in "Scheduled Hours" for today and future. The hours return for past dates before Yesterday and Today are incorrect. I am comparing this data in a table matrix by bringing in Actual Hours and Scheduled Hours side by side. 2 Day Lag Hours = VAR CurrentDate = SELECTEDVALUE('Tables- Dates'[Date]) VAR Result = SWITCH( TRUE(), CurrentDate < TODAY() - 1,[Actual Hours], //Past: Actual Hours Measure CurrentDate <= TODAY(), [STAR Scheduled Hours], //Today and Yesterday: Scheduled Hours Measure CurrentDate > TODAY(), [STAR Scheduled Hours] //Future: Scheduled Hours Measure ) RETURN Result Thank you!
afree
2 years ago Place DAX Commands and Tips
557Views
0likes
2Comments
switch() and SelectedValue for multiple filter choices
I am currently trying to build a measure that has three variables. To return the values, I used Switch() to allow for the selection of filters. When I use distinctive values, the measure works perfectly. But I get the alternate result back when I choose two filters. Is there any DAX expression I can use to create combinations of filters and decide what the response needs to be? My measure currently VAR A = Calculate (DISTINCTCOUNT(TableA), Dim_A[SalesType]="ABC") VAR B = Calculate (DISTINCTCOUNT(TableA), Dim_A[SalesType]="DEF") VAR C = Calculate (DISTINCTCOUNT(TableA), Dim_A[SalesType]="GHI") SWITCH ( SELECTEDVALUE ( Dim_A[SalesType] ), "ABC", A, "DEF", B, "GHI", C, A+B+C) Ideally, I would like to be able to create combinations where I say If chosen values are "ABC"&"DEF", give me A+B, "DEF"&"GHI", give me B+C, etc.
Solved
Anonymous
3 years ago Place DAX Commands and Tips
7.3KViews
0likes
4Comments
Tabla ignora filtro objeto visual leído en variables
Hola, tengo éste código que me ignora el filtro del objeto visual HF= VAR M1 = CALCULATE(MAX(Calendario[Fecha])) VAR MaxDate = SELECTEDVALUE(Calendario[Fecha], M1) VAR M0 = CALCULATE(MIN(Calendario[Fecha])) VAR MinDate = SELECTEDVALUE(Calendario[Fecha], M0) VAR ND = (MaxDate - MinDate) +1 VAR T1 = CALCULATETABLE( SELECTCOLUMNS( 'Usuarios Iniciales', "LApp", 'Usuarios Iniciales'[LApp], "User_Id", 'Usuarios Iniciales'[user_Id], "Date", 'Usuarios Iniciales'[timestamp.1]), 'Usuarios Iniciales'[timestamp.1] <= MaxDate && 'Usuarios Iniciales'[timestamp.1] >= MinDate) RETURN T1
Solved
Anonymous
3 years ago Place DAX Commands and Tips
448Views
0likes
1Comment