Explicit filter argument of Calculate function : Not able to understand its evaluation
Hello Members, I have a query with respect to working of calculate filters and its evaluation context. Some Background I have a sample data. I have a task to compute the closing balance of customers. I got the desired result in the form of following table matrix. The cell level and sub-total level results make sense to me except the one highlighted in red circle I got this result through following piece of DAX code. balance1 = SUMX( VALUES(Balances[Name]), CALCULATE( SUM(Balances[Balance]), LASTNONBLANK( 'Date'[Date], COUNTROWS(RELATEDTABLE(Balances)) ) ) ) What is my Issue? My understanding of calculate function is that it evaluates its "EXPLICIT" filter arguments in original filter context. In case of this example. LASTNONBLANK is an explicit filter. In case of Grand total (circled in red), their original filter context do not have any active filter context i.e it has all the unfiltered names (Balances[Name]) and all the dates ('Date'[Date]). If we evaluate LASTNONBLANK function in its original context as explained above, it should return 18th July 2010 as the date which will be an explicit filter argument for Calculate function based on which it will evaluate its expression. Now, due to sumx iterator, it creates a row context on values(Balances[Name]) table and results into context transition. In case of context transition, it would put implicit filter of names for each row. Explicit filter (i.e the result of LASTNONBLANK function) will be applied on top of the result of context transition. As both the filters are not over-riding, we should get something like this for Grand total Just to be clear, i am getting the desired result with this DAX computation. Nevertheless, i am posting this question to seek help from this wonderful community as to which step above am i failing to understand? Supporting Info below The configuration of Matrix table is as follows I have just two sample tables in the data model which are linked to each other with Data column Link for PBIX file https://drive.google.com/file/d/1dt3d6p6fJjszsylM_6jj0sY5NSOIcSzc/view?usp=share_link My basis of the above question is based on my understanding of the working of Calculate function as documented in dax.guide Thank you so much everyone for patiently reading and attempting to help here. Regards ARU
Cumulative sum in column until it finds the certain value
Hi, I need to write this to the Explicit measure in the Power Pivot: Start with cumulative sum in column (One by one; It can be ascending or descending.), when it hits the first 0 in the cell, stop the calculation and show the sum from 1 row to the row where is the 0 placed. Can anybody help with the measure? I have only 0 or 1 in the Column. When it is needed I can replace 0 for „Null“ or some text. Thank you, Jan Ps: I additionally added the picture and sample data. Sample data: https://ulozto.cz/tamhle/MNXyzonmetMQ#!ZJZkMwR2AzHlZGpkZwx2BGIuAzZ3Z1SAqTIlH0cBMGuiomOxZD==
