Debug DAX measure variables incl. virtual tables
Hi, I created an idea to be able to visually inspect the variables of a DAX measure, when the DAX measure is used in a table visual, line chart visual, etc. Please vote 😀 https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=a7a31361-2f40-ef11-b4ac-6045bdbf86b4
Variavel para calcular duas datas para um mesmo resultado
preciso calcular o que esta sendo entregue atrasado, porem são duas colunas com datas diferentes, como poderia calcular as duas em uma mesma coluna e me retornar o que esta em atraso ou não? não estou conseguindo pensar nessa variavel na foto eu consegui calcular apenas a previsão da entrega, mas possui outra coluna que eu preciso calcular junto, para realmente verificar se foi entregue em atraso ou não, a coluna seria Dt.Sla
NETWORKDAYS overriding Variables
Hello, I was struggling with the new function NETWORKDAYS, that does not give the proper value at total. I insvestigated, and end up, to the following DAX to put in evidence the issue ; Duration in work days from Release date to Last Ship Date = Var selection = ADDCOLUMNS( CALCULATETABLE( FACT_CUSTOMER_ORDER_LINE_C, FILTER(FACT_CUSTOMER_ORDER_LINE_C , FACT_CUSTOMER_ORDER_LINE_C[Date First Released] <> BLANK() && FACT_CUSTOMER_ORDER_LINE_C[Date Last Actual Ship] <> BLANK() && FACT_CUSTOMER_ORDER_LINE_C[Date First Released] >= DATE(2022,01,05) ) ) , "Date First Released bis" , FACT_CUSTOMER_ORDER_LINE_C[Date First Released] ) VAR list_with_days = ADDCOLUMNS( selection , "calc_duration_work_day" , VAR selected_site = SELECTEDVALUE(DIM_SITE[Site]) VAR list_exception = UNION( CALCULATETABLE( SELECTCOLUMNS('Calendar WORK_TIME_EXCEPTION_C', "exception date" ,'Calendar WORK_TIME_EXCEPTION_C'[EXCEPTION_DATE] ), TREATAS({selected_site},'Calendar WORK_TIME_EXCEPTION_C'[SITE] ) ) , {[Date Last Actual Ship] } ) RETURN NETWORKDAYS([Date First Released bis] , [Date Last Actual Ship] , 1 , list_exception ) ) return CONCATENATEX(list_with_days , FACT_CUSTOMER_ORDER_LINE_C[Date First Released] & " to " & FACT_CUSTOMER_ORDER_LINE_C[Date Last Actual Ship] & " give " & [calc_duration_work_day] , ", " , FACT_CUSTOMER_ORDER_LINE_C[Date First Released] ,ASC ) Here below is the resulut I et. I thought the the duation of days would be stored in the variable "list_with_days" and frozen. But t seems the duration of days by line given afterwards has a different value. Does anyone already met this kind or issue ? Thanks in advance to the community for the support. AnthonyRun DAX formulas based on values/criteria to produce the required output.
Hi ... I have data like below for my dashboard. So far I'm having trouble with the expected results for item no. 4 and no. 5 as table below. As we can see, the table above has values that indicate that the KPI indicator is either increasing or decreasing. Achievement column is the activity result for each items. How can I get the result as in column status?... Can someone help me and advise on this. Regards.
Applying multiple filters on table (with the goal of avoiding repeated filters)
I have a report with slow visuals (to the point that it runs out of memory) and I am trying to optimize some of the DAX measures. It is a big report, but the model for the part that I am having problems has 4 tables shown below: and the current measure is: Capacity = (CALCULATE(SUM(FactCapacityForecast[Value]), FILTER('Shared DW_DimVersion', EDATE(MIN(DimDate[Date]),-1)='Shared DW_DimVersion'[FCStartDate]), FILTER('Shared DW_DimAccount','Shared DW_DimAccount'[AccountNumber]="CALC0008"), DATESBETWEEN(DimDate[Date],MIN(DimDate[Date]),EDATE(min(DimDate[Date]),5))) + CALCULATE(SUM(FactCapacityForecast[Value]), FILTER('Shared DW_DimVersion', EDATE(MIN(DimDate[Date]),-1)='Shared DW_DimVersion'[FCStartDate]), FILTER('Shared DW_DimAccount','Shared DW_DimAccount'[AccountNumber]="CALC0006"), DATESBETWEEN(DimDate[Date],MIN(DimDate[Date]),EDATE(min(DimDate[Date]),5)))) * (DIVIDE( CALCULATE(SUM(FactCapacityForecast[Value]), FILTER('Shared DW_DimVersion', EDATE(MIN(DimDate[Date]),-1)='Shared DW_DimVersion'[FCStartDate]), FILTER('Shared DW_DimAccount','Shared DW_DimAccount'[AccountNumber]="CALC0017"), DATESBETWEEN(DimDate[Date],MIN(DimDate[Date]),EDATE(min(DimDate[Date]),5))), CALCULATE(SUM(FactCapacityForecast[Value]), FILTER('Shared DW_DimVersion', EDATE(MIN(DimDate[Date]),-1)='Shared DW_DimVersion'[FCStartDate]), FILTER('Shared DW_DimAccount','Shared DW_DimAccount'[AccountNumber]="CALC0016"), DATESBETWEEN(DimDate[Date],MIN(DimDate[Date]),EDATE(min(DimDate[Date]),5))))) * (DIVIDE( CALCULATE(SUM(FactCapacityForecast[Value]), FILTER('Shared DW_DimVersion', EDATE(MIN(DimDate[Date]),-1)='Shared DW_DimVersion'[FCStartDate]), FILTER('Shared DW_DimAccount','Shared DW_DimAccount'[AccountNumber]="CALC0005"), DATESBETWEEN(DimDate[Date],MIN(DimDate[Date]),EDATE(min(DimDate[Date]),5))), CALCULATE(SUM(FactCapacityForecast[Value]), FILTER('Shared DW_DimVersion', EDATE(MIN(DimDate[Date]),-1)='Shared DW_DimVersion'[FCStartDate]), FILTER('Shared DW_DimAccount','Shared DW_DimAccount'[AccountNumber]="CALC0006"), DATESBETWEEN(DimDate[Date],MIN(DimDate[Date]),EDATE(min(DimDate[Date]),5))))*-1)*-1 I believe this can be written in a more efficient and readable way. Right now the filters are applied on 3 tables ('Shared DW_DimVersion', 'Shared DW_DimAccount' and 'DimDate') in each of CALCULATE functions and 2 of those are just being repeated. I want to create a table variable where I first apply filters on 'Shared DW_DimVersion' and 'DimDate' only once and then use that variable to apply filter on 'Shared DW_DimAccount' afterwards. Maybe the final measure can be something like this: Capacity Optimized = VAR intermediate_table = CALCULATETABLE(FactCapacityForecast, //KEEPFILTERS( FILTER( //ALL ('Shared DW_DimVersion'[FCStartDate]), 'Shared DW_DimVersion', EDATE(MIN('DimDate'[Date]),-1)='Shared DW_DimVersion'[FCStartDate] ) // ) , KEEPFILTERS( DATESBETWEEN(DimDate[Date],MIN(DimDate[Date]),EDATE(min(DimDate[Date]),5)) ) //DATESINPERIOD('DimDate'[Date],MIN('DimDate'[Date]), 5, MONTH) ) RETURN (CALCULATE(SUMX(intermediate_table, FactCapacityForecast[Value]), KEEPFILTERS( FILTER( //ALL ('Shared DW_DimAccount'[AccountNumber]), 'Shared DW_DimAccount', 'Shared DW_DimAccount'[AccountNumber] = "CALC0008" ) ) ) + CALCULATE(SUMX(intermediate_table, FactCapacityForecast[Value]), KEEPFILTERS( FILTER( //ALL ('Shared DW_DimAccount'[AccountNumber]), 'Shared DW_DimAccount', 'Shared DW_DimAccount'[AccountNumber] = "CALC0006" ) ) )) * DIVIDE( CALCULATE(SUMX(intermediate_table, FactCapacityForecast[Value]), KEEPFILTERS( FILTER( //ALL ('Shared DW_DimAccount'[AccountNumber]), 'Shared DW_DimAccount', 'Shared DW_DimAccount'[AccountNumber] = "CALC0017" ) ) ), CALCULATE(SUMX(intermediate_table, FactCapacityForecast[Value]), KEEPFILTERS( FILTER( //ALL ('Shared DW_DimAccount'[AccountNumber]), 'Shared DW_DimAccount', 'Shared DW_DimAccount'[AccountNumber] = "CALC0016" ) ) ) ) * (DIVIDE( CALCULATE(SUMX(intermediate_table, FactCapacityForecast[Value]), KEEPFILTERS( FILTER( ALL ('Shared DW_DimAccount'[AccountNumber]), 'Shared DW_DimAccount'[AccountNumber] = "CALC0005" ) ) ), CALCULATE(SUMX(intermediate_table, FactCapacityForecast[Value]), KEEPFILTERS( FILTER( ALL ('Shared DW_DimAccount'[AccountNumber]), 'Shared DW_DimAccount'[AccountNumber] = "CALC0006" ) ) ) )*-1)*-1 The results are supposed to be used in a matrix alongside 'DimDate[Month]' values. I have been playing around with different functions, but cannot get the same number as the original measure. How can I get the correct measure? Thanks! Moe OwenAuger I think you have a good solution for this problem as well 😄
