table variables

5 Topics

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
frithjof_v
2 years ago Place DAX Commands and Tips
1.2KViews
0likes
2Comments
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
NBi
3 years ago Place DAX Commands and Tips
371Views
0likes
0Comments
Run 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.
Solved
NickzNickz_BI
3 years ago Place DAX Commands and Tips
1.2KViews
0likes
6Comments
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 😄
Solved
Anonymous
3 years ago Place DAX Commands and Tips
1.3KViews
0likes
2Comments
Trying to access table variable fields in return command and count rows in the filtered table.
Hi Everyone, Table template: ID Product Amount 1 FOODS 2000 2 Fertilizer 5000 3 Livestock 5000 4 Unallocated 6000 5 FOODS 7000 3 Fertilizer 2000 7 Livestock 5000 8 Unallocated 5000 3 FOODS 6000 4 Fertilizer 7000 1 Livestock 2000 1 Unallocated 5000 13 Unallocated 5000 I am trying to create a measure where I need to count the number of IDs who contribute to 20% of total amount in descending order. I created the below dax, everything works fine in Dax studio but the result is different when implemented in Power BI, checking if someone can help in fixing it. Below is the detailed description of what i am trying to do. GM Cumulative = -- Creating a summary of amount grouped by ID and Product ------------------------------------------------------------------------------------------------------------------------------------------------ VAR tbl = SUMMARIZE ( CALCULATETABLE ( FILTER ( ALL ( Leaderboard ), Leaderboard[EOM] > [Date Start] && Leaderboard[EOM] <= [Date End] -- Date Start and Date END End changes based on the selcted value being MTD/YTD/R12 && LEFT ( Leaderboard[Name], 11 ) <> "UNALLOCATED" ), CROSSFILTER ( Leaderboard[EOM], dimDate[EOMONTH_DATE], NONE ) ), 'Leaderboard'[Employee ID], 'Leaderboard'[Product Division], "SUM_AMT", SUMX ( Leaderboard, 'Leaderboard'[GROSS_MARGIN_AMOUNT] ) ) ---------------------------------------------------------------------------------------------------------------------------------------------- -- Table two ranking the summed value and sorted in Descending order VAR tbl2 = ADDCOLUMNS ( tbl, "rank", RANKX ( SUMMARIZE ( CALCULATETABLE ( FILTER ( ALL ( Leaderboard ), Leaderboard[EOM] > [Date Start] && Leaderboard[EOM] <= [Date End] && LEFT ( Leaderboard[Name], 11 ) <> "UNALLOCATED" ), CROSSFILTER ( Leaderboard[EOM], dimDate[EOMONTH_DATE], NONE ) ), 'Leaderboard'[Product Division], 'Leaderboard'[Employee ID], "SUM_AMT", SUM ( 'Leaderboard'[GROSS_MARGIN_AMOUNT] ) ), [SUM_AMT], , DESC, SKIP ) ) -------------------------------------------------------------------------------------------------------------------------------------------- -- Table 3 is creating a new column with cumulative sum based on Rank ( 1st row will have 1st rank value, 2nd row will have summed up values of rank 1 and 2 and so on..) VAR tbl3 = ADDCOLUMNS ( tbl2, "Cum_Sum", SUMX ( TOPN ( [rank], tbl2, [sum_amt], DESC ), [Sum_AMt] ) ) --------------------------------------------------------------------------------------------------------------------------------------- --Table 4 summing up the total value of amount to use in return (to compare 20% of it with Cumulative sum) VAR tbl4 = ADDCOLUMNS ( tbl3, "Total_Sum", SUMX ( tbl3, [SUM_AMT] ) ) RETURN ----------------------------------------------------------------------------------------------------------------------------------------------- -- Returning the number of rows whose cumulative sum is less than or equal to 0.2 of total sum return CountX(filter(tbl4,[CUM_SUM]<=0.2 * [Total_Sum]), Leaderboard[Employee ID]) The output is correct for MTD, YTD and R12 (Rolling 12 Months) in Dax studio. Whereas its correct for MTD in Power BI but not for YTD and R12. Looks like the expression in return command is not respecting the cross filter applied in the table variables. Its always returning the count of rows whose value is same as MTD. @fenixen can you please have a look on this one?
Solved
rrr
4 years ago Place DAX Commands and Tips
1KViews
0likes
1Comment