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


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 :: How To Filter Tabular Perspectives

Oct 5, 2015

I have been living in the T-SQL world for years, and am working on my first Tabular model.

I have a fact table that I would like to use for many reports. My idea is that I would use Perspectives in the tabular model to pull in the columns that I need for each purpose. In some cases, I also need to filter out some rows. Is there a way to filter perspectives? The only solution I am coming up with is to create views in SQL Server and import the fact table multiple times for each purpose. That doesn't seem very efficient.

View 2 Replies View Related

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 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 :: 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 :: MDX - Aggregate Measure For Specific Time Range / Filtering And Print Only Current Month

Aug 6, 2015

At the following MDX code , I want to get the aggregate of measure only for  members that are also in the specified last time (like in examp 01/06/2015) . I tried existing and exists, but without any lack.

WITH MEMBER A AS
(b)+(C)
MEMBER [Measures].[Aggregate] AS
Aggregate(DAYTIME].[Month].&[2013-01-01T00:00:00]:[DAYTIME].[Month].&[2015-06-01T00:00:00],
([Measures].[D])

[Code] ....

View 13 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

Analysis :: Tabular Refreshed Automatically?

Jun 18, 2015

My boss and I attended the SQL conference in Vegas a few years ago. He remembers a session on tabular where the presenter said when the underlying table that tabular is built on changes, it automatically refreshes the tabular db.  Now that we're working on a tabular project I'm looking around for this information and don't find anything like that happens.  From what I can see, the refresh either has to be scheduled from a task, or manually refreshed by right-clicking the db and selecting "process db".  He said the session presenter was a Microsoft Program Manager too.  Is there any truth to what he remembers? 

View 2 Replies View Related

Analysis :: Tabular Model - Optimization

Jul 2, 2013

I have a Tabular model, it has 8 tables , 7 of the tables process in 3 mins, but the main table is 22.3gb in size//I am processing the table on a 64 bit pc with 8 gb ram to the local instance from a Production server//At this rate it will take days to process the Main table 22.3gb, the main table was running off a view but I have inserted the data into TableA and updated the model///Only other optimization process I can think of is copying TableA to my local machine but the performance to the Server on the network is not an issue.I am just process the main table for the last 4 hrs and it is only at 11.7 mill records out of 124 mill total @ this rate it will take 44 hours

Size: 22342752 KB
CREATE
TABLE [dbo].[StagingTablevwKeyEventsfact](
[PKID] [bigint]
NULL,     
[ManufactureGeoLocationCode]
[nvarchar](14)
NULL,
     
[code]....

View 6 Replies View Related

Analysis :: Tabular - DAX - SUMMARIZE And RANKX

Sep 2, 2015

According to the book "Microsoft SQL Server 2012 Analysis Services - The BISM Tabular Model" (pages 276 and 277) the following DAX query should work. However, I'll get a rank of 1 returned for every product name. My impression is that ALL doesn't block the context transition for product name. Because there is nothing in the errata, I might do something wrong!? The second query is just a workaround giving the correct result.

EVALUATE
CALCULATETABLE(
    SUMMARIZE(
        'Internet Sales',
        Product[Product Name],
       
[code]....

View 2 Replies View Related

Analysis :: Tabular Refresh Times Out

Aug 3, 2015

SQL 2014...In SSDT, as I'm developing my tabular model I often add new columns to the underlying table/view that feeds my model.  When I go into Table/Table Properties, select the new column I've added then hit OK it spins and spins then times out.  My model has just over 1 million records.  The only way I can get it to work is to delete the table and then add it in again.

View 2 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 :: Convert Tabular Model To Multidimensional

Sep 25, 2015

We have a requirement to convert Tabular model to Multidimensional as few functionalists like actions does not support in tabular. Looking for list of things needs to take care to convert tabular solution to Multidimensional.

View 2 Replies View Related

Analysis :: Tabular DAX / Calculate SUM Against Unrelated Table?

Nov 4, 2015

I tried using the page below but wasn't able to quite properly adapt it to my scenario

[URL]

I have two UNRELATED (can't be related for reasons outside the scope of this, other relationships already exist) tables.

Each table does, however, contain a WorkerID:

WorkerTimesheets:

WorkerID, HoursLogged, Date
3,10,2015-05-05
3,6,2015-05-10
4,8,2015-05-01

Then the unrelated table "ConstructionSites"

SiteID, SiteManagerID, SiteOpen,SiteClosed
A5, 3,2015-01-01,BLANK

What I want to do is be able to show a measure that SUMS the number of hours logged by anyone who is a SiteManager from the ConstructionSites table.

I wanted to do a SUMX of WorkerTimesheets against HoursLogged, but FILTER against WorkerTimesheets[WorkerID] = ConstructionSites[SiteManagerID] so only workers who are also SiteManager would be counted.However, I can't seem to get that to resolve it always throws an error along the lines that it can't determine context.

View 3 Replies View Related

Analysis :: Adding KPI To SSAS Tabular Perspective

Aug 1, 2012

SQL Server Data Tools within VS 2010 Shell.2012 SASS in Tabular Mode.I've created a Measure and KPI in my fact table.  I've created a perspective named "Summary"  When selecting the fields and measures for display in the perspective, the measure is available, but not the KPI (target, status, etc.). When Analyzing in Excel with the Summary perspective, the KPI is not available in the field list. When analyzing in Excel with the (default) perspective, the KPI is available.As far as I can tell, there is no way to select a KPI within the perspective window. 

View 8 Replies View Related

Analysis :: Creating Tabular Model On OLTP

Nov 4, 2015

I have been looking at implementing a tabular model based on an OLTP database that's not dimensional. I know that this is possible but during my proof of concept I have encountered numerous problems ...

The things that I have run into are: After setting up the relationships I have found that measures filter context don't propagate along the relationships as I would expect. if the measure is coming from a target table and not a source then an ALL member is returned ( as in multi dimensional when a dimension isn't related to a measure group). Given the lay out of an OLTP database this will be hard to avoid.

One thing I have done to try an mitigate the above problem is to combine the tables used for measures in a view and using that in the source to connect to the rest of the tables. however due to the tables being of different grains this has then created duplication in some of the keys and measures. so the keys cant be used in relationships and the measures aren't accurate.

Are these things other people have come across? or should I give up the ghost and just recommend using dimensional models for the source? is tabular just geared towards a DW the same as multidimensional?

View 2 Replies View Related

Analysis :: Server Tabular And Power BI In Azure?

May 12, 2015

getting a complete Microsoft DW/BI (Tabular), Power BI and Office enviroment up running. I am a (Microsoft) DW/BI manager and want to learn about the latest Microsoft DW/BI (SQL Server Tabular) and Power BI (Power Pivot, Power View, ...). To save time & access from anywhere, I thought I could use Windows Azure but quickly got lost in the Azure preview and market place! I even contacted the Windows Azure team and that didn't clarify things.

So, how to get a SQL Server (2014) environment with all DW/BI (SSIS, SSAS, SSRS, Data Mining) technologies together with Power BI and Excel working? If so, how to get started.If this is not possible on Azure and it has to be done on Window, what do I need to install for the Power BI stuff (to work with Tabular)? I already have SQL Server 2014 and Office Pro Plus installed natively on my Windows machine.

View 6 Replies View Related

Analysis :: Deploying Tabular Model Drops Records

Oct 22, 2015

I'm using SQL Server 2012 Bi edition.

I created a model in Visual Studio 2012 and when I process the model, most tables (the ones that should) process 77,546 records and that is reflected in VS.

However, when I deploy the model to the server it is only deploying 76,500 records for those tables.

Where those 1,046 records went to? Is there a setting that limits the records to be deployed? My base tables in the Data Mart have 77,546 records, just as in my Visual Studio model.

View 3 Replies View Related

Analysis :: SSAS 2014 - How To Change Mode To Tabular

Nov 13, 2015

I have a new 2014 SSAS installation.  During VS2013 Tabular Project create I get an error Cannot connect, Reason The workspace database on server ***** is not running in tabular mode.I've changed    msmdsrv.ini  - DeploymentMode>2...The server wont start.

View 3 Replies View Related

Analysis :: SSAS Tabular Stand Alone Table In Excel

Jul 16, 2015

I am working with SSAS Tabular. I have a stand alone table with 60 columns and contains 120K records. Table size is 250MB. And trying to build a tabular report out of it and it is taking longer and throwing exception, screenshot attached.

It might be cross-join issues, as workaround created a dummy measure and using in report. But it working for 10-20 k records and beyond throwing same exception. I have 8 GB RAM and 100 GB free disk space.

Customer wanted it in SSAS Tabular...

View 3 Replies View Related

Analysis :: SSAS Tabular And SSRS - Implementing Security

Oct 1, 2015

i would like to pose a question, when a user do a login in SSRS server,is  it possible to pass the username to SSAS tabular, to implement security? I've tested using USERNAME() creating a role that relates USERNAME() with a dim user username, but now i need to implement it with the username used in SSRS server. 

View 2 Replies View Related

Analysis :: SSAS Tabular - Preview Of Calculation Not Working

Oct 30, 2015

Does any1 know why preview of a DAX calculation does not work? I mean once you enter the calculation in the bottom of the table it doesnt´t show up.

View 8 Replies View Related

Analysis :: Processing SSAS Tabular Model Automatically

Oct 18, 2012

how can i automatically update my tabular model in the future when there's an update in my database.

View 4 Replies View Related

Analysis :: SSAS Tabular - Grouping In Excel Grayed Out?

Nov 10, 2015

I have a Tabular data model and I'm returning a measure that counts employees (each row is an employee) and then a calculated column in the model that gets SeniorityInMonths.

So if an employee was hired exactly 1 year ago, they would have 12 in this column.

I want to group these into bins, but the Group option is grayed out.

View 6 Replies View Related

Analysis :: Tabular Not Possible To Use Order As Per Database In Pivot Table

Aug 25, 2015

We couldn't find a way to show row/column members in the order as in the database. The Pivot table always applies alphabetical sorting: 

Is there really no way to show items as per database order?

View 4 Replies View Related

Analysis :: Tabular Dynamic Security With Multiple Values

Aug 6, 2015

It seems simple but I'm not able to make it works. I've got a fact table with sites (DimImplantation) with allowed users (DimDroit) :

1 user can have access to many sites. I've tried these codes but only one worked and only in case where the user had only one site :

a) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME() ) ==> worked for 1user with 1 site

b) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME(); DimDroit[DimImplantation_ID];DimImplantation[DimImplantation_ID] ) ==> doesn't work and says 'not able to to identifie the value of DimImplantation[DimImplantation_ID]
in the current context.

c) CALCULATE(VALUES(DimDroit[DimImplantation_ID], SUMMARIZE('DimDroit',DimDroit[Utilisateur]), DimDroit[Utilisateur]=USERNAME()) ==> version for  SQL2012. I've had 'End of input reached'.

View 3 Replies View Related

Analysis :: Ignore Unrelated Dimensions In Tabular Model

Jul 11, 2013

how to set 'Ignore Unrelated Dimensions' property in Tabular Model.

View 3 Replies View Related

Analysis :: SSAS Tabular Model Processing Error

Nov 4, 2015

I get the following error while processing a SSAS tabular model (2014) on a new server.The SSAS service on this server is running under a login which has access to the SQL server data sources. I tried changing the provider to OLEDB from SQLCLNI11 in the connection string but that doesn't work too. The error message isn't useful to debug further.

The cube processing succeeds on a different server. I scripted out the cube DB and ran it on the new server and am trying to process full but it fails with the following error.

Error Message:
The operation failed because the source database does not exist, the source table does not exist, or because you do not have access to the data source.

More Details:
OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.

For more information see SQL Server Books Online.; 08001; SSL Provider: No credentials are available in the security package

; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001.
A connection could not be made to the data source with the DataSourceID of 'd7a37dae-be87-44e0-a8b2-498069af82c9', Name of 'connection name'.
An error occurred while processing the partition 'XXXX_460f3467-1a99-4dc9-aaf2-bcf3d54a5c4c' in table 'XXXX_460f3467-1a99-4dc9-aaf2-bcf3d54a5c4c'.

The current operation was cancelled because another operation in the transaction failed. The operation failed because the source database does not exist, the source table does not exist, or because you do not have access to the data source.

View 3 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







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