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.Previous User Selected Value
I don't think this is possible, but I thought I'd ask here in case I'm missing something. Imagine a scenario with a table that has a single column called Item. In this column are the values A-E. Item is used in a slicer. I would like two measures: [Selected Item] and [Previously Selected Item]. The DAX for [Selected Item] is simple. In this example, if the user were to select the slicer option "D" and then subsequently change the selection in the slicer to option "B", is it possible to set up [Previously Selected Item] to return "D"? As far as I know, there is no history of the filter context, but I wouldn't be surprised if there was some some set of functions out there that would allow for this. See the below screenshot for the desired result in the scenario described above:
