Analysis :: How To Apply A Filter To Decide Which Formula Is Used In A Calculated Measure
May 8, 2015
The user wants to be able, using excel, to apply a filter to all measures in every measure group. I though that I can create a dimension with a single level with two members, let´s say "on" and "off" and depending on the selected member and using an IIF statement decide which formula applies to the calculated measures.
I have serious doubts about the performance and for this technique because I am thinking as a .Net developer and not as a cube developer. Maybe it is better to resolve it scoping the measures but I cannot figure it out.
In my cube there are two measures which are used in different calculations.Now I'm need to show in report if there any months in data when both or even another one of the measures is not updated (value = 0 or NULL).
how should I create the calculated measure for that?
I have tried in mgmt studio to plan this but I'm in a loop of errors.
I am trying to count a set as a calculated measure, when this set is called directly in the row , it returns fast, but when i try to count the set as calculated measure(so i can slice with another dimension) the query keeps running forever.
The queries are below
select {} on 0, nonempty ( {([Transaction].[RPC Count].&[1],[Transaction].[Account ID].[Account ID])} , {([Account].[PAYMENTSTATUS].&[0],[Account].[Account ID].[Account ID])} ) Â on 1
How can I apply "Min" formula under a "new measure" (calculated field) within a pivot table under Power pivot 2010?Can see that neither does it allow me to apply "min" formula directly "formula box" nor could find any other option.Intent formula: "=Min(1,sum(a:b))" this isn't allowed so all I can do is "=sum(a:b)".
I'm trying to create a percentile rank function based on the standard WIKI version:
I've seen Brian Knight's article here, but that only deals with percentile.
Where I'm struggling is getting the count of members in a set using a measure, in the current context on the same hierarchy, as the filter expression. I'm using the comparative set as in belonging to the same geographical location, and therefore associating by another attribute.
So, cl as below:
MEMBER [Measures].[RegionPercentileCount] AS Count( Filter( NonEmpty( descendants(Ancestor( [Supplier].[NameMap].CurrentMember, [Supplier].[NameMap].[Region]), [Supplier].[NameMap].[Supplier Id]), [Measures].[ActiveMeasure]) , [Measures].[ActiveMeasure] < ([Supplier].[NameMap].CurrentMember, [Measures].[ActiveMeasure])))
Using the same measure and context hierarchy is always going to be equal, and therefore the count is always zero. Its almost as if I need a nested context for the FILTER which allows me to use enumerate the set on the same hierarchy whilst maintaining the external reference.Â
I'm thinking that perhaps I'm going to have to create another hierarchy and use that as the filter set and reference through StrToMember or similar.
Is it possible to filter out a measure only at the intersection of Two dimension members? I have a date dimension, Â a Hospital dimension and a wait time measure.
For Example, is it possible to filter out Wait time for Bayside Hospital for the Month of June 2015?
I want Wait time to continue to be displayed for all other months and roll up into the totals without the filtered value.
i want to create a new measure that will behave based on the dimension dropped,ex. if i added the employee dimension only it will aggregate data from the #Calls Count but if i added the product dimension it should display # Product Calls at the product level and #Calls Count at the employee level as shown in the screen shot.
One of my models has order data, cost per order/invoice ID and then dimensions on Fiscal Year, category, etc...the usual.
A user wanted to search it for an exact order amount. (They knew for example that one of our accounts was not balancing by single order worth $746.13 and assumed it must be an order that was placed but never marked shipped that slipped through the cracks).
Now, in the model I have "order amount" as a field and then a measure that sums that.
I could expose that "order amount" field as a label and let them filter on it in Excel (and that works).
However, I haven't had any luck filtering on the actual measure "Total Order Amount". Such as OrderID-> View Filter -> "Total Order Amount" equals 746.13.
I assume this is due to a few things:
Measure calculates at different levels so filtering on a measure is difficult as you would have to place all the "slicers" and set them first before the measure would "exist" at a level where it could be $746.13. Orders by year would have $746.13 as part of it's year sum, but wouldn't exist as a stand alone line item orders by year 2015 might be 2 million.Â
Orders by category might exist at 500,000, 8,000, 15,146.36, etc... but not $746.13.
So I would need OrderID on there as a column so the measure could return at the value of $746.13 for one row for it to match the filter?
Basically: 1. Why it can't really filter on a measure? 2. Is there a better way to accomplish this other than exposing the actual column in the fact table "order amount" as it feels like that could cause all kinds of confusion if other users try to slice/filter on that not realizing exactly what it is meant to be?
In SSAS, I want to add a calculated measure and set that measure as the Parent of existing measure. For example I have the measures as A and B from the fact table. Now i want to add a calculated measure as C and set this measure as Parent for measures A and B. How to do that in SSAS.
Hi,I'm struggling to get a calculated column to work in sql, the fields to be calculated are:[AdRevenue_a] money[Admissions_a] int[DoorPrice_a] smallmoney[DoorSplit_a] moneyAnd the calculation I require is:(AdRevenue_a / ( (Admissions_a * DoorPrice_a) - DoorSplit_a )) * 100This is what I think it should be but it doesn't work...convert(decimal(6,2), ((AdRevenue_a / ((Admissions_a * DoorPrice_a) - DoorSplit_a))*100) ))Any suggestions??
I am having trouble with a calculation that on the surface seems to be working until it is opened up in excel.
The measure calculates the Actual Value as of last year and is as follows - ([Measures].[Act Val],ParallelPeriod([Time].[Year],1))
This works fine until I look at the totals within EXCEL 2002/2003
1)If I choose to view a whole year then the total shows me a whole year. That is fine and as expected.
2)If I choose part of the year the total shows me the whole year. This is wrong and am wondering if it is an Excel probem or my calculation? But here is the weird part -
3)If I choose another year and select the same months as I have already chosen (ie Year 20023 Jan, Feb and Year 2003 Jan and Feb) then the total for the year that is already selected works!!(shows only the total of the two months - whereas the extra year I have selected (remember - Jan Feb only) shows me a total for the whole year.
WAIT - There's more
4)If I add another month to the 2002 selection (ie add March) then the total for 2003 (Jan Feb only) includes the March value also (even though it is not selected)!!!!!!
Hello! Given three dimensions in my cube (Product, Calendar, & Sales). I'm trying to create a calculated measure that will sum 8 weeks of sales starting from the third week in which sales started. For instance, weeks 1-4 had zero sales. Week 5 is the first appearance of any sales. Therefore, the measure would sum the sales of weeks 7-14 (8 weeks starting with the 3rd week after sales begin).
Any ideas?? Keep in mind that every product is going to be different (sales start in different weeks).
I have a calculated measure that is "Cost Per Patient:=sum([ActualCost]) / sum(Patients[PatientCount])" and it works fine. I'd like to be able to create another calculated measure based on "cost per patient" that uses min, max and average of "cost per patient" but dax wont let me.
I've read about dax calculate but not sure how to use it in my situation. Here is background on the problem:
There are 2 fact tables, prescription cost and patient count. Both are related by a practice dimension. The practice dimension has a geography hierarchy of county - town - practice.Â
The "Cost Per Patient" works fine with the geography hierarchy. By that I mean it shows the correct value when looking at the different levels. For my new calculation, for example looking at the list of counties, I want to use the min, max and average of "Cost Per Patient" of all counties. To break the problem down, I was going to start with creating and testing separate calculated measures for min, max and average and that's where I am stuck.
My understanding of the problem is it's something to do with the context of where the calculation is being made. As I want to perform the min, max and average on all the children of the parent. This article explains it but I can't figure out how to apply it to my situation [URL] ....
We have a client that wants to take the Max value of a count at the Product level, but then for each Brand (Parent of product), sum the Max values for that particular slice. The example below shows the Max sales per month over the last 6 months.Â
Prod1 had it's best month in Month2 (20 units) and Prod2 had its best month in Month4 (30 units). Brandx should roll up to 50 units. Can DAX handle this in a single Calc Measure?
Brandx         50    Prod1       20    Prod2       30 Brandy         60    Prod5       40    Prod6       20
I have a calculated measure in Power Pivot that takes too long to run that it would max out my RAM when running it in Excel 2016 (for some reason it actually manages to run in Excel 2013 but still takes a long time). How to optimise the measure? I have shared the measure below. it is the Unique Leads (MTD) measure that I need to run in a pivot table that is broken down by everyday in a month. The Unique Leads (MTD) measure basically calculates the month-to-date unique leads where a unique lead is defined as an email address that has not appeared in the 'application' table thus far in a particular month.
I have a dataset that needs to return "Fatalities Involving drivers age 15-17". For years 2003 , 2004, 2005.
So i set up 3 different filters: Year : 2003,2004,2005
Person type: Driver
Age : 15,16,17
Now this is fine for getting a crash count and fatalities (for teen drivers). But i want the fatalities for everyone ( All Person Types) and (All Ages) ...how do i go about this. All i get right now, is the fatalities for Drivers 15-17.
I try to calculate the turnover of an employee based on all sales on this customer and the hours spends working for this customer.To simplify, I have 2 tables.General Ledger table.I have customer no and sales.And activity table,I have customer no, hours and users.To be able to calculate the average of a customer, I made an average value based on the sales/divided by the hours on activity table.Customer 1 sales of 100$.Value of the customer is 10$/H -> John 40$ / Mike 60$.How can I use the information 10$/h to use it with the hours from a employee.
Robert Bruckner [MSFT] in his response to "This field is missing from the returned result set" says that rsMissingFieldInDataSet is "only a warning"
I have a report with 7 simple calculated measures to show counts for 7 ratings: ([Measures].[Score Card Count],[Attainment].[Attainment - ATTAINMENT].<rating>).
6 measures work fine; one, for which there is no rating in the fact table returns rsMissingFieldInDataSet warning. Even worse, if I use this field in an expression in the report with other measures, the return is always nothing.
In other words, although this is "only" a warning, it actually causes errors by not returning values from expressions.
I tried to check for null/nothing but could not prevent "breaking" the expressions. Any ideas?
I have a requirement in Power Pivot where I need to show  value based on the Dimension Column value.
If value is Selling Price then Amount Value of Selling Price from Table1 should display, if Cost Price then Cost Price Amount Should display, if it is Profit the ((SellingPrice-CostPrice)/SellingPrice) should displayMy Table Structure is
I've created a calculated measure which is a division between 2 other measures which i also have displayed in the cube. of course as soon as there is some level of aggregation, the shown result is an averaged division and therefore is wrong.
here is some example: A 16874 B 956 C 1354 D 264 E 103 F 81 G 6 H 3 X 23 Total 5198.36...
here the wanted result would be a simple some of all the other values (=19664)
How could i treat totals/aggregation differently or have any good way of solving this issue? I know there is the aggregatefunction propertie for normal dimension, but i'm not familiar with calculated measures and it seems properties that can be accessed through BIStudio's interface is rather limited.
thanks a lot in advance for your help. I've been looking for tweaks on the data to trick the cube, but couldn't find any way of getting the result i'm looking for.
I have written a simple DAX calculated column formula using SEARCH and got an error: It tells the text "PWP" cannot be found but as you can see the screen shows this text exsist within "Promotional Claim"The same happens when I try FIND function.
I have the following scenario. I want to apply some calculations on different levels and then aggregate them up.
First measure calculates at Productgroup,color,store,size level
ProductGroup Color Store Size Amount Quantity
Measure:Amount*Quantity  (ProductGroup, Color, Store, Size)
A Blue Store A L 100 6 600 A Red Store A S 150 4 600 A Green Store A M 160 7 1120 B Blue Store A L 300 3 900
[Code] ........
The other measure ignores color
ProductGroup Store Size Amount Quantity
Measure:Amount*Quantity  (ProductGroup, Store, Size)
A Store A L 100 6 600 A Store A S 150 4 600 A Store A M 160 7 1120 B Store A L 640 15 9600
[Code] ...
Ignoring that gives another figure for productgroup B. In the pivot, I should see both measures at whatever attribute, except for the measure that excludes color will be null if tried split on color
ProductGroup Amount Quantity (ProductGroup, Color, Store, Â Size) (ProductGroup, Store, Size) A 410 17 2320 2320 B 640 15 2820 9600 C 170 5 430 430
My dataset to display is coming from a stored procedure. Now based on a report parameter which user selects, I want to filter the results being displayed but only if user selects certain value in the parameter drop down. I tried using iif statement and I can use it to filter dataset based on parameter but can't get how NOT to apply the filter for a particular value of the user selected parameter.
We have a fact table where one of the measures in that fact table is invoice balance. Some of our reports requirements list that they only want to see the sum of those invoices where the balance is greater than X dollars. How do I go about filtering calculations based on a measures value?
Any way to create a measure that filters the second column to mimic the behavior of only filtering for slicer for the Open Pool Date values as per the image below. Ultimately, I need to create a measure that only includes accounts that were opened 6 months prior to the month row context.
Trying to get the values in the second column to only include aging accounts 6 months prior instead of 12 months prior.
I need to translate a user€™s regional setting into one of our own language codes before I send it through as a filter to the model query. If our language codes were the same, the filter would look like this in the report filter - Language Code = GetUserCulture() Which translates to this in the database query (for us english) - table.language_code = 'EN-us' And of course I need it to look like this - table.language_code = 'ENG'
I would like the logic to be globally available to all report writers (ie not forcing each report writer to have an iif or case stataement). I was thinking custom assemblies or maybe a database function, but at this level of the filter, I cannot seem to figure out how to embed a database function call to apply to the filter criteria like this Language Code = dbo.ConvertFcnIWrote(GetUserCulture()) Or how I would access the custom assembly in the filter expression.
Do you have a recommended implementation for this situation?
