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.
Trying to add dax to calculate all filters besied one.
Hi Guys, I have used this dax on my fct_table: % of Instance Total Cost Test = DIVIDE( Sum(Fct_EA_AmortizedCosts[CostInBillingCurrency]), CALCULATE( SUM(Fct_EA_AmortizedCosts[CostInBillingCurrency]), Fct_EA_AmortizedCosts[PricingModel] = "OnDemand" ) ) Page Filter is also "OnDemand". But it is always returning 100%... Why? Generally i want to divide row filter context in table by sum of all this rows: but it is somehow giving 1,00 (100 %)... Can anybody help ? Goal: Keep filter context in table and always take current row sum of costs and divide by total row created from filter context... why this is not working what i did? Best, Jacek
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:
Have calculations only work in the filtered context
I hope this is a simple question and I don't know what to actually google. This is just a sample of what I actually want to do but this functionality applies to so many things i want to do. I have a table that has 2 columns. [TestName, Log Time] Every test ran for a different period of time and my goal is to calculate how much time is left in the test at any specific log time. I'm making a line graph over time. TimeRemaining vs LogTime. The formula for this is simple TimeRemaining = Max(LogTime) - LogTime. The issue that Max(LogTime) for the data set is larger than Max(LogTime) for any particularly TestName that i'm filtering to. When i filter my line graph to an individual test name how do I ensure that the filter applies to the TimeRemaining calculation? As you can see in the picture below the TimeRemaining is not 0 at the end of this test since the context of my filter is not correctly applied to the calculation.
Filter context in dax with relationship clarify
Hello everyone, first of all, I study Definitive guide to dax vol2, reading blogs of sqlbi.com etc..so please do not respond that I should watch it I still fighting witch filter context, I have following easy use case based on following tables schema is simple And here starts my questions (not only one) if I want to write DAX measure which will count user per each city, 1) why I cannot do it with following formula? User per City = CALCULATE(COUNT(User[ID_user]),Location[ID_location]) result is this: when there is connection between data, connection is in fact table. Data can be matched and calculated in here, why it is not possible to do it in "mid table" ? 2) if I will allow both directional ways it works it means that ALWAYS when I need to do calculation in target table (table users, because users are calculated) filter context must be linked to each other ? 3) Is it possible somehow to write DAX measure from question 1) to meet my goal without adjusting Data Model ?? thank you so much ! still try practice DAX and read articles about it, thanks for deep dive explanation if possible !! thanks in advance
How to force DAX measure to 0 in the same filter context
I have a measure calculating the sales amount and i'm placing it insiede a line chart with months on the x-axis, a field coming from a table called 'CALENDAR_TREND'. The chart is filtered by a slicer with months, field coming from another table called 'CALENDAR' linked to my previous table 'CALENDAR_TREND'. I want my chart to show values of sales amount, when the measure returns blank, as zeros but doing so mutates the chart filter context showing all the month of a year and placing 0 whenever the measure is blank or the month is outside the range selected with my month slicer. I just want the chart to show the range of months i selected with those having blank sales amount forced to zero. I already tried using ALLEXCEPT but i was not able to solve my problem. Example: I want the value of Sales PY to be forced as 0 in the month of August. If i put a new DAX measure usign ALLEXCEPT and add +0 to the calculation of the sales amount, i'll get a chart with months from Jan to Dec even if i filter those values out. Any clues will be appreciated, THANK YOU! 🙂
Keeping and ignoring filtercontext over different tables in one measure
Hi all, Our customer would like to see - in the tooltip, so secundary information - the totaal number of employees for the selected year for a selected level of the organisation (it's a hierarchie of three levels). The data model looks like this, where the relationship between the two facts is inactive. I realise this is not an optimal data model, unfortunetaly we have to deal with this right now. My goal is to get a sum of the 'EMPCOUNT'. To do this, I believe I need a measure that keeps the filters on the highlighted columns, but ignores the filters on everything else. So far, the closest I got was this: sum_empcount = CALCULATE( SUM(FACTEMPCOUNT[EMPCOUNT]) ,USERELATIONSHIP( FACTTICKET[FACTEMPCOUNTKEY] ,FACTEMPCOUNT[FACTEMPCOUNTKEY] ) ,ALLEXCEPT( FACTTICKET ,FACTTICKET[DIMDATEKEY] ,FACTTICKET[DEPARTMENT_L1] ,FACTTICKET[DEPARTMENT_L2] ,FACTTICKET[DEPARTMENT_L3] ) ,REMOVEFILTERS(DIMEMPLOYEE) ,REMOVEFILTERS(DIMTRAIN) ) But this measure still ignores the filtering on DIMDATE, so I get a sum over all the years instead of the selected one. What is the right DAX expression for this problem?How does SELECTEDVALUE work?
Hi, I'm trying to create a personal budget report. What I'd like to achieve is to select a month from a slicer and have a matrix visual give me my cash flow for the selected month by account. But what I also want is to see what the cash flow was the previous month and compare the two. The problem is that I use a custom calendar whereby instead of my months starting on the 1st and ending on the 30th or 31st, I tweaked it to coincide with when I get paid. I get paid on the 25th of each month, so I want my months to start on the 25th and end on the 24th of the next month. So I created a table for this. Here is the data: The calendar table: The budget month table: Here are my relationships: Here is my DAX formula for Actual Last Month: But I get no result (see Actual Last Month): current_month variable returns nothing. In the total row, it gives the correct month number. So this makes me think I should probably add the date somewhere to have the date in the row context and not just in the filter context. In the Budget slicer, I used the Budget Name field from the Budget Month table. Is the correct field to select? How can I tweak my DAX or my model to give the correct actual last month based on the selection made in the Budget slicer? Please help.
