Why Do My DAX Formulas Return Wrong Results?
In this session, you will learn how to avoid common pitfalls and mistakes when building calculations using DAX within Power BI. We will explore 10 different DAX calculation examples that return unexpected results and learn how to avoid them, and how to fix them. Through solving these mistakes, you will also learn and brush up on your DAX fundamentals such as Evaluation Context, Iterators, Context Transitions, and Expanded tables concepts.
Erroneous Results from COUNTROWS() in Context-transition
Unless I'm missing something, I'm pretty sure this isn't supposed to happen: EVALUATE CALCULATETABLE ( CALCULATETABLE ( ADDCOLUMNS ( VALUES ( Users[User ID] ), "Test Expression 1 - CORRECT", CALCULATE ( CONCATENATEX ( FILTER ( VALUES ( 'Privilege Conflicts'[Privilege 2 ID] ), 'Privilege Conflicts'[Privilege 2 ID] IN VALUES ( Privileges[Privilege ID] ) ), 'Privilege Conflicts'[Privilege 2 ID], " | " ) ), "Test Expression 2 - INCORRECT", CALCULATE ( COUNTROWS ( FILTER ( VALUES ( 'Privilege Conflicts'[Privilege 2 ID] ), 'Privilege Conflicts'[Privilege 2 ID] IN VALUES ( Privileges[Privilege ID] ) ) ) ) ), CROSSFILTER ( 'Users and Privileges'[Privilege ID], Privileges[Privilege ID], BOTH ) ), Users[User ID] IN { 102, 103 } ) Results: Notice how, for User ID = 102, the results in the test expressions are inconsistent, despite the fact that they are semantically basically the same. For reference, note that the following produces expected results: EVALUATE CALCULATETABLE( CALCULATETABLE( { ( -- Test Expression 1 - CORRECT CALCULATE( CONCATENATEX( FILTER( VALUES( 'Privilege Conflicts'[Privilege 2 ID] ), 'Privilege Conflicts'[Privilege 2 ID] IN VALUES( Privileges[Privilege ID] ) ), 'Privilege Conflicts'[Privilege 2 ID], " | " ), Users[User ID] = 102 ), -- Test Expression 2 - CORRECT (inconsistent with prior query) CALCULATE( COUNTROWS( FILTER( VALUES( 'Privilege Conflicts'[Privilege 2 ID] ), 'Privilege Conflicts'[Privilege 2 ID] IN VALUES( Privileges[Privilege ID] ) ) ), Users[User ID] = 102 ) ) }, CROSSFILTER ( 'Users and Privileges'[Privilege ID], Privileges[Privilege ID], BOTH ) ), Users[User ID] IN { 102, 103 } ) Results: --- It appears that something might not be functioning correctly between the context-transition and COUNTROWS(), but not fully sure. marcorusso Jeffreywang --- Power BI version: 2.139.2054.0 Data model:
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 ARUNot able to comprehend working of Rankx function in DAX
Hello, I am quite new to DAX and still learning various aspects of this language. While working on contoso database, i wanted to compute rank based on category-wise sales, even if the granularity of the table is deeper than that. Following is the table i want to build. The granularity of this table is at Product code level while my computation supposedely should happen at a product category level. My understanding is that Rankx creates a lookup table by iterating an expression as the 2nd parameter and rank the lookup table values based on the order (ASC or DESC depending on 4th parameter). The 3rd parameter which is an expression gets evaluated as per the current row context. I created this following measure in order to arrive at the rank of the category sales category wise ranking := RANKX(ALL('Product'[Category]), CALCULATE(SUMX('Product',[Sales Amount])), -- Invokes context transiiton [category wise sales]) the dependent measures are as follows : category wise sales := CALCULATE(SUMX('Product', [Sales Amount]), ALLEXCEPT('Product','Product'[Category])) Sales Amount := SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ) This output gives me the ranking of "1" in all the rows of Power BI table as per the screenshot above. However, i expected it to give me the rank based on category wise sales. So "Music, Movies and Audio Books" category should have "8" as the rank, while "Home appliances" should carry a rank of "1" I think the above DAX code works in steps given below : Step 1. For each row of Power BI table view, it evaluates the first parameter (table expression). As the table has an outer function "All", it returns all the values of the Product category as a table. Step 2. The 2nd expression iterates through the table output from Step no.1 above (all categories) and gets category wise sales through context transition. These category wise sales act as a lookup table for ranking 3. The 3rd expression gets evaluated in the current context and returns category wise sales against each row of the powerbi table view. This scalar value gets compared with the lookup table values and generate a ranking. I am sure that there is something wrong in my understanding of the steps. above as the output is different from the desired result. Having said that, I can achieve the desired result result through following measure. But i am not sure why my previous piece of code generates incorrect result. category wise ranking := RANKX(ALL('Product'[Category]), [category wise sales]) Desired output as below : One can find the PBIX in the following link (Refer Page 3) https://drive.google.com/file/d/185eE645t2I0EX9joWTwnQ87pt9cLp0I2/view?usp=sharing Thank you everyone for your help. Regards ARUMultiple 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
Regarding to the concept of context transition
Hi there, I would like to ask a question about context transition, the problem I met is described as follows, In my data model, Table 'Sales' and 'Product' has a Many to one relationship through [Product ID]. I want to add a calculated column in 'Product' to get the [ProductWithSalesQuantity] by means of context transition. The 1st formula I use is ProductWithSalesQuantity = CALCULATE(SUMX(Sales,'Sales'[Quantity])) And the data shown up is what I expected. However, the 2nd formula I use shows that context transition doesn't happen. The formula is ProductWithSalesQuantity V1 = SUMX('Sales',CALCULATE(SUM('Sales'[Quantity]))) Which I think by using the iterator of SUMX, CALCULATE shall trigger context transtion. And then I try to add a Table as follows. By using the iterator of ADDCOLUMNS, CALCULATE does trigger context transition. The result is what I expected. Could anyone guide me why the 2nd formula I use didn't work?
Simple CALCULATE measure is testing my fundamental DAX understanding
Hi All, This simple CALCULATE measure is testing my fundamental DAX understanding and I would like to draw from your wisdom. The following screenshot should explain the situation, but I will explain further below: I have this table: Code Category Period 1 A P1 2 A P2 3 B P2 4 C P1 I created a measure: "Count Of P2" to count the number of rows with Period = P2 as follows: Count of P2 = CALCULATE(COUNTROWS('Table'),'Table'[Period] = "P2") which is equivalent to: Count of P2 = CALCULATE( COUNTROWS('Table'), FILTER( ALL('Table'[Period]), 'Table'[Period] = "P2")) When I apply a filter on Category = A or B and Period = P1, I would expect the measure to remove the filter on Period and replace it with Period = P2, resulting in an overall combination of filters as Category = A or B and Period = P2, which should result in only two rows being counted by Countrows, but the result I get is only 1 row counted. I hope someone with more experience can help me understand where I am getting it wrong. Much appreciated, Mariano
Context Transition Different with Measure Reference Even with CALCULATE
Hi, I'm struggling to grasp why I'm experiencing the following. When I use a reference to Measure_1 (which begins with CALCULATE) in Measure_2, I get one result. When I simply copy the text from Measure_1 into Measure_2, I get another result. I understand that I should expect this if Measure_1 didn't begin with CALCULATE because of the context-transition ocurring with measure-references. However, since Measure_1 begins with CALCULATE, shouldn't the context-transition be the same regardless of if I reference Measure_1 or use its code? Why am I getting different results? Measure_1 := CALCULATE ( VALUES ( Date_Table[Week_ID] ), FILTER ( Date_Table, Date_Table[Date] = MAX ( Sales_Table[Order_Date] ) ) ) // Date_Table is marked as the date-table. // Date_Table[Date] 1 -------------- * Sales_Table[Order_Date] Measure_2 := CALCULATE ( SUM ( Sales_Table[Sales_Amount] ), FILTER ( Date_Table, Date_Table[Week_ID] = [Measure_1] ) ) /* The above returns a different result than just copying the code from Measure_1 into Measure_2: Measure_2 := CALCULATE ( SUM ( Sales_Table[Sales_Amount] ), FILTER ( Date_Table, Date_Table[Week_ID] = CALCULATE ( VALUES ( Date_Table[Week_ID] ), FILTER ( Date_Table, Date_Table[Date] = MAX ( 'Sales_Table'[Order_Date] ) ) ) ) ) */ Again, I was expecting the results to be the same since CALCULATE begins Measure_1--i.e., context transition ocurring due to the implicit CALCULATE in the measure-reference or explicit CALCULATE in the pasted code. Any guidance would be much appreciated. A specific reference to where I can find an explanation in the Definitive Guide to Dax (or elsewhere online) would be appreciated as well if possible. Many thanks.
