create dax measure to remove context filter
hi everyone im trying to create a measure that remove the conext filter in a table visual with if statement condition i was trying to use 'all' function to remove the filter from the column but still not working. current measure : calculate(sum('categories trans'[achievement]), all('categories trans'[Base_type]) in the above example , i want to ignore the filter by (base_type) column in one category (zeed) and keep it for the other categories. for example: if category= 'zeed' i want the achievement measure to return 441 and for other categories i want the achievment measure to return same values above.
Multiple row Context within Iterators
Hello Community, I am trying to compute a table using DAX with date wise sales and its running average of last 5 days. I have this following code which works fine. However i reached here after some trial and error and i am unsure why the other DAX which i tried didn't work when i thought they should work (based on my limited knowledge and experience) DAX - Version 1 - Works Fine running_avg_table = VAR date_sale_table = SELECTCOLUMNS ( SUMMARIZE ( Sales, Sales[OrderDateKey], 'Date'[Date] ), "Date", 'Date'[Date], "sales", [Sales Amount] ) VAR final_table = ADDCOLUMNS ( date_sale_table, "running_avg", AVERAGEX ( FILTER ( date_sale_table, [Date] >= ( EARLIER ( [Date] ) - 4 ) && [Date] <= EARLIER ( [Date] ) ), [sales] ) ) RETURN final_table Desirable output - DAX Version 2 - Unable to understand why this version do not work. In this version, I have brought AverageX function in a seperate variable and used Sumx as an outer function just to have multiple iteration of date_sale_table. running_avg_table_1 = VAR date_sale_table = SELECTCOLUMNS ( SUMMARIZE ( Sales, Sales[OrderDateKey], 'Date'[Date] ), "Date", 'Date'[Date], "sales", [Sales Amount] ) VAR avg_last_5_days_sales = SUMX ( date_sale_table, AVERAGEX ( FILTER ( date_sale_table, [Date] >= ( EARLIER ( [Date], 1 ) - 4 ) && [Date] <= EARLIER ( [Date], 1 ) ), [sales] ) ) VAR final_table = ADDCOLUMNS ( date_sale_table, "running_avg", avg_last_5_days_sales ) -- invokes context transition RETURN final_table Output from DAX 2 I have two queries with respect to this piece of code. 1. I think there are 3 row context being created when defining "avg_last_5_days_sales" measure. First by the outer function Sumx, then by Averagex and lastly by Filter function. However, i think the DAX recognises this to have 2 row context only. This is evident from the fact that i cannot set "2" as an integer value for the 2nd parameter of Earlier function. I am not sure where i am going wrong with this? 2. Why variable "avg_last_5_days_sales" when used as a measure call in "final_table" variable do not yield the average of sales for the preceding 5 days period? My argument here is that as this is a measure call, it should have "calculate" as the implicit outer function. Addcolumn being an iterator should result into context transition in that case and should provide the average of the last 5 days sum. When i use Averagex function directly in the final_Table (as in case of correct DAX version), i am getting the correct answer. Supporting Material / Info - One can refer to the PBIX in the following drive link https://drive.google.com/file/d/1zwmulRDtPdA766Q6ixruYKDVFR6jddu-/view?usp=share_link From Table relationship perspective, sales is a fact table, while date is a dimension / date table. Thank you everyone for your time investment in attempting to help me Cheers ARU
Measure calculation using row context
Hi; I am try to calculate a measure NumDaysOpenShop, in a Period Year/Month and in a Shop or in all shops LIke page filters i have got a Year and a Shops, in the year i can select only one, in the shop i can select one, some or all shops. In matrix i show year/month on rows 2022 Measure be SUM([Table][Colum]) with dates in 2022 and shop selected in page filter, except if all or more than one shop is selected, in this case i want use MAX(measure [NumDaysInMonth] with dates in 2022 January SUM([Table][Colum]) with dates in January 2022 and shop selected in page filter, except if all or more than one shop is selected, in this case i want use MAX(measure [NumDaysInMonth] with dates in 2022 Hope i explained .......
