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.
Dynamic data from API while scrolling the table
Hello All, We have a custom on-premise REST api which is providing data in chuncks. Now we want below features to be added on the report- 1. To call the API while scrolling the data. In such when the user scolls the page the report will automatically call the API and refersh the visuals accordingly. 2. Filtering- For filtering it is required that the user selects all the filters on the report and when he clicks on a button it will make an API call and API will do the filtering and provide the result to report. With the data returned by API will reflect on the visual. Is it possible in Power BI? Thanks in advance.
