Running total on a large scale dataset
Hi all, I'm having a 80 million record dataset. When I was doing the POC report that has only account number, I used the following fomular to generate my balance: Balance = [Opening balance] + CALCULATE( SUM(transaction[Amount]), FILTER( ALLSELECTED(transaction), [Index] <= MAX(transaction[Index]) ) ) The above fomular works exactly as what I want. However, when I load not all my data, but just over 10 million data, this fomular gives me an error message saying that it's running out resources. Then I thought about dividing my original transaction table into pieces first as for my report, anyway, one account number and only one account number must have been selected. I used the following fomular to divide my table: FilteredTable = CALCULATETABLE( 'transaction', FILTER ( 'transaction', 'transaction'[Account_number] = SELECTEDVALUE('transaction'[Account_number]) ) ) It gives me an empty table, if I replaced "SELECTEDVALUE('transaction'[Account_number])" by a fixed text string, new table will be created with data. Anybody can help me on this issue? Thanks in advance.
Sum Values within a criteria
Hi Community! I need a measure what sum the lowest price of every product (matching the ID), according to the schema below (our price and competidor price): When i dont select a competidor, it just sums all the rows following date and store filters: Can someone help me?? :((How to filter table using preselected slicer text value
Hi all, I have initial data table "dummy" consist of (minimum) two column in which one of them can be used as a filter. The filter set is in column 'country' and expect the user to either select the country, either one (ie. NO), more than 1 (ie. NO, MY), or none mean all selected to get filtered table based upon it. this is the code that i put at the filtered "table" expecting user input in slicer. Seems like this is simple coding, i try to find references related to it, but still got no luck. Later I'm gonna use the filtered table for further measure so that I need to filter the initial table. if someone can help to fix this. Table = VAR __selectedValue = SELECTEDVALUE(CountrySlice[Value]) RETURN CALCULATETABLE(dummy,FILTER(ALL(dummy),dummy[country]== __selectedValue)) Thank youDAX Fetch base value of 1 column and multiply it to all the rows of another column
Need help in DAX. I have one table that contains data related to multipliers for each year, like the follows: *********************************** Table Multiplier ******************************** Another table is there which has multiple 'values' for the year column like the follows: ****************************************** Table Entities ************************************************* There's a 1- * relation between the tables based on the year column. Question: What I have to achieve is access the base/starting"Value" of every entity (base year being 2011-12) and multiply only that value by all the rows in the 'Multiplier' column. The solution for entity A would look something like below: Base value remains 20 for every year, then that value gets multiplied by the multiplier which changes every year. Now, to get the Base value, I used the following to create a measure: Base value = CALCULATE(SUM('Entities'[Value]), Multiplier[Year] = "2011-12") For the base*multiplier column: Base * multiplier = [Base value] * SUM('Multiplier'[Multiplier]) The result of the above is: So, it is generating correct base value but then not moving forward to multiply each year's multiplier with the base value. I Tried other filter contexts too using KEEPFILTER, ALL within the CALCULATE, but nothing seems to generate the value I want. Please help. Thanks in advance. Some people I know who are good and can help are tagged, but others are most welcome to help too. v-chenyue-msft parry2k Anonymous amitchandak lbendlin Ashish_Mathur
Percentile of a table stored in a variable
Hi all, I would like to detect outliers in my daily data report by using the percentile function. I have a detail table (table 1) and I want to calculate the percentile of the second table which shows the tickets per day. If I create a fixed calculated table I have to add "ALL / AllExcept" function inside Percentile function: Percentile = PERCENTILEX.EXC (ALL(TicketsByDate), TicketsByDate[Count_Tickets], .90 ) I need to apply external filters and I don't want to rely on the All/AllExcept function. For this reason, I want to store my calculated table in a variable. The problem is that I can't calculate the percentile of this calculated table in a variable. I get the following error: "If k is not a multiple of 1/(n + 1), PERCENTILEX.EXC will interpolate to determine the value at the k-th percentile." Can someone help me? Thanks!!!
Marking data from previous week to be labelled carry over if they are still pending in current week
Thank You in advance for Your help. Please see pics. I'm trying to counts item from previous week, which are still pending in the current week as carry over. as seen in the pic Apr13 as nothing pending, then I should not get any carry over for the following of april 2nd which april 9. but here I get some data(6) which is not correct. Here the Dax formula I used Carry Over = VAR MaxDate = MAX (Week[Date] ) RETURN CALCULATE( [Pending Tasks], FILTER( ALL ( Week[Date] ) , Week[Date] = MaxDate - 7 ) ). Thank You very much for Your help.Filter sales on dates where every customer has a recorded purchase
Amit Hi, I have a standard model, with a transaction table, a date table and a customer table (10 customers). What I need is to calculate sales, but only for days where every customer have a recorded purchase. That means that if I have no customers selected in my slicer, only sales for days where all 10 customers made a purchase should be filtered, if I select 2 customers in my slicer only sales for days where both these 2 customers made a purchase should be filtered, and so on. This needs to be done dynamic, so I have so far created the following measures: Sales = SUM ( TransactionTable[Value] ) Number of Customers Filtered = COUNTROWS ( VALUES ( CustomerTable[Customer] ) ) Number of Customers in TransactionTable = COUNTROWS ( VALUES ( CustomerTable[Customer] ) ) Sales Filtered = CALCULATE( [Sales] , FILTER( VALUES( '01_FACT_Forecast_SpotPrice'[ValueDateCET] ) , [Number of Customers in TransactionTable] = [Number of Customers Filtered] ) ) This provides me with the following result: So far so good, Sales Filtered excludes datetimes where there is only 1 customer (I have choosen 2 customers in this example). The problem emerges when I want to add customer as a dimension. Then I only get the total, no split on individual customers: Clearly, my approach is not correct, so any help would be much appreciated
