Analysis :: Filter A Set Based On Same Measure?

Jun 10, 2013

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.

View 3 Replies


ADVERTISEMENT

Analysis :: MDX Filter A Measure Based On Another Measure

May 3, 2015

I want to filter a measure based on another measure (both are measures on the same FACT table).Distinct Number of Users HAVING.User Cost above 0.

I tried doing having but because it’s two measures and not a tuple with a dimension it writes an eror.My query is something like:

SELECT
  [Measures].[Distinct Number of Users ]
having [Measures].[User Cost] >0
 ON
COLUMNS,

[code]....
 
Please note, that I want it in a MDX query not needing to change the cube or DWH table.

View 4 Replies View Related

Analysis :: Filter Measure On Intersection Of Dimensions

Jul 24, 2015

SSAS 2008 R2

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.

View 4 Replies View Related

Analysis :: Tabular - Filter / Search For A Specific Measure Value?

May 14, 2015

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?

View 3 Replies View Related

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.

View 3 Replies View Related

Analysis :: Dynamic Measure Value Based Upon Role / Department

Sep 28, 2015

I have an issue related to SSAS security. We have an SSAS multidimensional cube which needs 3 types of security:

- Access to the entire cube => OK, based upon a role
- Restricted access to one department (= dimension) => OK, based upon a role
- Access to the entire cube, but with dynamic security on 2 measures.

Let's say, we have 2 departments (food and non-food). Users within food are allowed to see sales and pieces from the food department, but not from the non-food department. 

It is not an option to restrict access to the non-food department because there are other measure which they have access to. I tried cell security, but this is very slow and generates multiple empty rows on my selections.

View 3 Replies View Related

Analysis :: SSAS - Display Measure Value Based On Other Attribute Member?

Sep 2, 2015

getting Correct Measure based on Member Present in Other Attribute .

I am working on SSAS 2012 and have cube build and ready ..

I have Two Measure in Cube 

[MEASURES].[Actual] and [MEASURES].[Target] and I need to create One more Calculate Measure 

I have dimension DimProduct 

I want to Display [MEASURES].[Actual] if Color "Purple" is present for PRODUCT1 Else Display [MEASURES].[Target].

MDX to create Calculate Measure for this logic?

View 4 Replies View Related

Analysis :: How To Make Calculated Measure Behave Based On Dropped Dimension

Jun 25, 2015

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.

View 7 Replies View Related

Analysis :: MDX Need To Filter Measures Based On A Dimension

Oct 26, 2015

I am pretty new to MDX and am having trouble getting what I need out of this MDX query. Some business rules:

Gross Amount applies to all clients, whether Type A or Type B.  I always want to return Gross Amount.Some clients are Type A, some are Type B, some are both, and some are neither.There are Type A Net Amount and Type B Net Amount values for all clients, but I only want to display the Type A Net value if the client is a Type A client, only Type B if the client is a Type B, or both for both, and neither for neither.  I would like to return blank/null, not $0.00, for those values that should not be displayed.

Here's the basic query.  

SELECT { [Measures].[Gross Amount],
[Measures].[Type A Net Amount],
[Measures].[Type B Net Amount]
} ON COLUMNS,
NON EMPTY {[Dim Client].[Parent Client Code].[Parent Client Code] *
[Dim Client].[Child Client Code].[Child Client Code] *
[Dim Client].[Is Type A].CHILDREN *
[Dim Client].[Is Type B].CHILDREN
} ON ROWS
FROM ClientInfo

Here's the DESIRED output........

View 3 Replies View Related

Analysis :: Calculated Measure To Check Measure Value Updates By Month?

May 13, 2015

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.

View 4 Replies View Related

Filter A Measure With MDX

Dec 27, 2005

HI all friends


I have a Fact table like this



DIMENSSIONSMEASURES
------------------------------
idempidprodcutsalessalesws




I just want make the next query with MDX


SELECT sales
FROM myFactTable
WHERE salesws > 0


Note that salesws is a measure not a dimmension


How it would be in MDX? I need a lot of help


Regards!

View 4 Replies View Related

Filter Measure

Jan 18, 2007

I have a delima.



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.



Please help..im confused!

View 5 Replies View Related

Filter Fact Table Via Measure Value?

May 20, 2008

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?

View 3 Replies View Related

Power Pivot :: Possible To Dynamically Filter A Moving Total Measure In DAX?

Oct 2, 2015

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.

Trailing6Month Conversion:=CALCULATE([TOTAL LTD Converted Amount] ,Filter(Settlement700,Settlement700[OpenDatePool]>=RELATED(DimDate[Trailing6MonthsEnd])))
Trailing6Month LTD Conversion Amount:=CALCULATE([Trailing6Month Conversion],DATESINPERIOD(DimDate[FullDate],LASTDATE(DimDate[FullDate]),-6,Month))

View 17 Replies View Related

Analysis :: Get Measure Value By Different Condition

Jul 17, 2015

I am very new to MDX. I have the sales amount measure and date dimension.

I need two measure sales value for the below condition. I am going to display this result in SSRS.

1. For between from date and todate
2. For last week range

View 2 Replies View Related

Analysis :: MDX - More Than 1 Measure On Output

Nov 12, 2015

Any example on how to output more than one measure in an MDX query. Lets say I want to display stdev(x) and average(x) (or any other two measures)?

View 7 Replies View Related

Analysis :: How To Get Unique Values For Each Measure

Sep 2, 2015

I am getting same value all over how to get unique values for each measures.

how to resolve it.

View 5 Replies View Related

Analysis :: Measure Value Needs To Be Manipulated In Cube

May 18, 2015

The data attached below is from a Fact table. When this data is browsed in the Cube the end user is only interested in value of Measure 1 when it is not equal to zero. Measure 1 is a base measure .how to suppress the value 0 for Measure 1 in the Cube.

DimesionKey1
DimensionKey2
DimensionKey3
Measure 1
Measure 2
Measure 3

[code]...

View 4 Replies View Related

Analysis :: Dynamic Calculated Measure In MDX

May 8, 2015

 I have a dimension called as DIM1 which has list of all measures and has an attribute called as ATTRMDX Formula. The formula will be like

([DIM1].[ATTR Measure Code].&[M1],[Measures].[ATTR MEASURE VALUE])+([DIM1].[ATTR Measure Code].&[M2],[Measures].[ATTR MEASURE VALUE]).

I want to pass this formula to a calculated measure as given below -

MEMBER [Measures].[FormMeasure] as ([Measure].[ATTRMDX Formula].currentmember.MEMBERVALUE)

but I get the string value itself as output, but when I put the formula as a string in the calculated measure I obtain the value.

View 2 Replies View Related

Analysis :: MDX - Count A Set As Calculated Measure

Sep 9, 2015

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

[Code] .....

View 5 Replies View Related

Analysis :: Split Wide Measure Group?

May 14, 2015

I have a wide fact table that I'm feeding to an SSAS cube. I was advised that splitting the measure group into two will improve performance when querying the cube.

I cannot find any documentation that supports this, in fact I get a blue curved line suggesting that I merge the measure groups since they have the same dimensionality and granularity.

I guess the best practice is what the blue line states, but without knowing the internals of SSAS I can undestand that a smaller measure group may be easier to handle, or create more specific aggregations for.

View 4 Replies View Related

Analysis :: Calculated Measure Under Folder In SSAS

Sep 2, 2015

How to put these calculated measures under the folder "sales line". i want to see it under the drop down...

View 5 Replies View Related

Analysis :: Drillthrough With Measures From Two Measure Groups

Nov 4, 2014

I'm trying to show measures from 2 measures groups in a drillthrough. Obviously, it's not possible with a standard drillthrough action, but I still hope that I can somehow achieve this with the ASSP GetCustomDrillthroughMDX function.

Speaking in AdventureWorks2008R2 terms: Imagine I have a pivot table with Product Categories in filter (say, filtered on Gloves) and "Internet Sales Amount" as measure. From context menu in Excel I can call the drillthrough action which shows me the individual sales records. I would like to show in drillthrough additionally "End of Day Rate" measure from "Exchange Rates" measure group.

One option would be to join FactInternetSales with FactCurrencyRate and make EndOfDayRate a physical measure in the "Internet Sales" measure group. This is a pretty huge overhead for my scenario and I'd like to avoid this.

Another one would be to call something completely external for a drillthrough (for example, a SRRS Report).

View 3 Replies View Related

Analysis :: Measure Group Execution Order

Sep 21, 2015

I am executing a CUBE which is having 9 measure groups, i need to know order of the execution of the measure groups.??

ex:
1. fsales
2.ftranssales
3.fitem
4.fstores..............etc...

I know, the dimension are executed first, but not aware of the order of execution of measure group.

View 4 Replies View Related

Analysis :: Where Clause On Measure Group In MDX Query

Nov 11, 2015

When I am executing below MDX query, it's giving correct result with out any issue 
                     
SELECT 
              NON EMPTY 
              {
                     [Measures].[Daystorecieve] ,
                     [Measures].[PO Recieved],
            [Measures].[Post Award Milestone PO Analysis Count],
                     [Measures].[Powith80pct Received]

[Code] ....

After the successful execution of the above query, I am trying to filter on my measure group [Measures].

[Daystorecieve]  values not equal to "0". With minimum number of the dimension selection my query executing fine.

Please find the below query.                                   

SELECT 
              NON EMPTY 
              {
                     [Measures].[Daystorecieve] ,
                     [Measures].[PO Recieved],
            [Measures].[Post Award Milestone PO Analysis Count],
                     [Measures].[Powith80pct Received]
              }  ON COLUMNS,

[Code] ....

But, when I am trying to execute with total number of dimensions. It's running long time and giving out of memory exception. Is there any way to apply where condition on my measure group like where [Measures].[Daystorecieve]<>0.

View 6 Replies View Related

Analysis :: MDX To Fetch Measure Source Table And Column ID

Jun 12, 2015

MDX query to get the source table and column for a measure from ssas database. i want below highlighted using mdx.

View 4 Replies View Related

Analysis :: How To Update Linked Measure Groups In SSAS

Jul 2, 2015

we are having an existing cube in that we need to update with new measures . The Measure groups are added to the cube as linked object. so when we are updating the measure group it is throwing the exceptions as follows..“Errors in the OLAP storage engine: The metadata for the statically linked measure group, with the name of 'SalesActual', cannot be verified against the source object.”

View 5 Replies View Related

Analysis :: Check To See If A Measure Is Of Certain Data Type In MDX Query

May 8, 2015

I am using the following approach to enable dynamic date calculations: [URL] ...

However, some measures I work with are percentages (not numeric values) and demand a different formula.

See example snippet bellow (first IIF statement)

/*Calendar*/
[Date Calculation].[Calculation].[Calendar Actual versus PY WK %] =
IIF
(
[measures].Name = "HSP2 %", -- < ThIs is where I am trying to check if the measure is a percetgae type
0, -- < When this is the case then do something different

[Code] ....

I am trying to access the metadata. I am aware of $system.mdschema_measures but I am unable to utulize this for my case.

View 6 Replies View Related

Analysis :: How To Get Last Processed Date For Each TABLE And Display In Measure

May 11, 2015

I can pull a last processed date for the entire model: URL...That works great for showing the last time anything in the entire model was processed.  It is not table specific. Updating / processing table A also changes the timestamp on tables B and C.However, if I want to look at just a specific table in the model (build a column for each fact table and a measure to go with it) I find that doing any process operation updates all the =Now() columns in all the fact tables.If I have a model with 3 fact tables and I do a process table using ProcessFull on one of them, all three tables calculated columns "LastProcessed"  =NOW() are updated. URL...

Type>ProcessFull</Type>
  <
Object>
    <
DatabaseID>MyModel</DatabaseID>
    <
DimensionID>TableA</DimensionID>
  </

[code]...

Is there a way to setup a measure in the model on a per table level to show last processed date for each individual table?  LastDataRefresh:= MAX ('TableA'[LastProcessed])

For example: Our sales table may update three times a day, where as our warehouse inventory table is only updated nightly.I wanted to let end users see by adding a measure for each table when the last process event was for a given table in the model.

View 2 Replies View Related

Analysis :: SSAS - Measure Creation On NVARCHAR Column?

May 26, 2015

I would like to know the possibility of creating Measure based on the below data.

I don't need to show any aggregate value. I need to show the value as it is.

View 6 Replies View Related

Analysis :: SSAS Design Tip To Measure Across Date Range

Jul 22, 2015

Developing a Retail cube using SSAS 2012. One of the dimension is DimCustomer with SCD type II. Each Customer can be a member or a non-member over a period of time. We have StartDt and EndDt to reflect the membership status.

eg:
Joe is a member between 06-01-2014 and 31-08-2014
Joe is a non-member between 09-01-2014 and 01-31-2015
Joe is a member between 02-01-2015 and 04-30-2015
Joe is a non-member between 05-01-2015 and 12-31-9999

Without adding fact row of Joe for each day to reflect the membership status, I want to provide the ability to measure "Active Customers Count" on a given date. There are 2 million customers in the DimCustomer Table.

View 2 Replies View Related

Analysis :: Rename SSAS Measure Created From Dimension

Aug 25, 2015

I have created SSAS Tabular model project.

I have dimensions like XYZID, Names, Zone etc.

When I create a measure using count function for XYZID. The name of the measure is Count of XYZID.

Its the same which gets displayed in Excel while I connect the model.

How to rename the "Count of XYZID" to ABC ??

View 2 Replies View Related

Analysis :: Cube Measure Does Not Show Any Data When Drag And Drop In Browser

Aug 26, 2015

I created SSAS cube in VS 2008 and have been able to deploy it successfully to the server. While creating the cube I was able to browse dimensions and all underlying tables just to make sure it has data. After deploying successfully when I drag and drop any measure group to browser it does not display anything.

The only thing I did different from straightforward cube building process was that when I created those measure groups the partitions that were created by default were giving me some unknown errors so I had to delete them in order for cube to process successfully.

Did that made any difference because I thought partitions are for improving query performance and has nothing to do with cube processing errors.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved