business-intelligence,powerpivot,dax,powerbi , Powerpivot: Subtract first member of group

Powerpivot: Subtract first member of group


Tag: business-intelligence,powerpivot,dax,powerbi

Consider the following table:

Group ID    Date
1001        2014-01-05
1001        2014-01-12
1001        2014-01-22
1002        2014-01-15
1002        2014-01-19

How may I calculate the Duration inside a group in days? Duration is defined as Date[k] - Date[0]

e.g.: The desired table should look like:

Group ID    Date          Duration
1001        2014-01-05    0  
1001        2014-01-12    7
1001        2014-01-22    17
1002        2014-01-15    0
1002        2014-01-19    4

I have tried using MINX to return the group's first Date value:

=MINX(FILTER(Table;Table[Group ID] = Table[Group ID]); Table[Date])

But the value returned is the entire table's minimum, as if FILTER is returning the entire table.


It's not clear whether you are trying to produce a Calculated Column or a Measure - you should probably be trying the later but it can be done either way.

The calculated column would simply be:

= [Date] - CALCULATE(MIN(Table[Date]), FILTER(Table, Table[Group_ID] = EARLIER(Table[Group_ID])))

Far more powerful are Measures! You could get this date

[Raw Measure] = MAX(Table[Date]) - CALCULATE(MIN(Table[Date]), ALL(Table[Date]))

This works because the ALL() opens up the filter context on the Date column whilst retaining the filter on the group.

You would need to iterate over this measure as it would object to the total, in this example you could use:

[Iterated Measure] = SUMX(Table, [Raw Measure])


How to configure my SharePoint server for Business Intelligence [closed]

What I need exactly to configure my SharePoint 2013 so I can use the business intelligence tools very well.

How to change the date format in Oracle BI Publisher?

How do I change the format of the date while creating a report using data model which I created using a simple SQL query which fetches aggregated data from a data warehouse? I need the date in a simple dd-mon-yy format. I am not able to locate where the format...

Qlikview reports / chart not generating or printing in colour

I have a qlik view report with a colour chart in it which isnt printing in colour, printer is set to colour and also the print preview is in B&W

PowerPivot % Difference from previous month

When trying to get the percent difference from one month to the next, January (or the first month in the dataset) will always show as blank. How would I go about making Excel look for the previous month of last year? I am using DateStream as my date table. Issues...

Microsoft Power BI Designer data model to Excel or PowerPivot

Is there a way to get a Microsoft Power BI Designer data model into Excel to work with in Powerpivot? Thanks in advance....

How do I identify like sets using SQL?

Using SQL Server, I have a table that looks like this: What I need to do is write a query to identify scenarios where the Name and Permissions field are equal so that I can give give them a unique Set ID. For instance, rows 2 and 4 would be...

PowerBI hierarchy creation without the cloud

Right now we can create hierarchies in PowerPivot but I'm pretty sure I can't do it in PowerBI. I could create a Data Model in PowerPivot and use that for PowerBI I think. Is there any other work around that could be more desktop based? Thanks! Mike...

Install SSMS for power-users of business data? Why not?

Question: As a DBA/BI Developer, should we install SSMS on the PCs of power-users of business data? What are the risks with this approach? Context: I love SSMS. It's ergonomically designed and enables not just the exploration and management of the SQL Server, but also the data within it (e.g....

Pivot Table with multiple rows all having the same level hierarchy

I have imported a bunch of data using PowerQuery into a single table and am building dashboard reporting. I have been using Pivot Tables to build my reports, which has worked fine so far. However, I've come to a point though where I want to simply show the count of...


OK, so I have googled up a lot on this subject and keep ending up with formulas which are too damn slow. I am suspecting if I split the formula in steps(Creating calculated columns), I might see some performance gain. I have a table having some numeric columns along with...

Count of Rows Based on Their Most Recent Value in Excel PowerPivot Using DAX

Is it possible to get a distinct count of rows based on a field's most recent value in a PowerPivot table using DAX? I have a transactional table that tracks professionals with hire and transfer dates. I would like to get a count of the professionals based on their most...

Can I load calculated measures from SSAS cube in Power Query?

Power Query Version 2.17 is supporting SSAS Cubes as data source now. When I connect to a cube I just see the standard measures of the cube and not the calculated members defined in the cube. Is there a way in Power Query to get access to the calculated members?...

How to create Power View report tiled by multiple KPIs?

I am trying to create a Power View report tiled by KPIs, is this possible? An example of my raw data: Company ID Employee ID Measure numerator denominator 1 01 1 2 5 2 04 1 3 6 3 02 1 0 5 4 03 1 1 2 1 01...

many to many powerpivot relationship

I have 2 data sets which i would like to create relationships for (there is a third i would like to join but the first 2 are priority). They all have a common link that is design_ID. I did have a picture showing what i have tried. but i do...

Top 5 and Bottom 5 records in Power Pivot

I am trying to show top 5 records and bottom 5 records in two separate tables in Power View. I have created a calculated measure to show the records. But it doesnt giving me the required result. My Data Set is:- ID Name Rating OverallRating Ranking 1 A 124 2...

Debugging SQL statement of SSIS SQL task to insert range of date

I'm trying to insert range of date into a date dimensional table using SQL task, and passing through parameters of BeginDate / EndDate to it. However, if I try to execute the package, there are no data inserted in the dimensional table, but the package executes fine. How do I...

Input controls overriding hidden table formula

I have a report I'm working on, where I have a series of tables attached to each other representing 18 months of our business year. Each table is being hidden if an input control of Months does not have the particular months listed, so only the most currently relevant months...

How to create nested tiles in Power View

I am currently able to use the tile feature in Power View to view data much more quickly. However I haven't figured out a way to have nested tiles to further drill down into the relevant data. For example, I want a tile strip at the top of my view...

How to bulk-import all Power Query workbook connections into PowerPivot?

Within PowerPivot for Excel, under the 'Design' tab, there's an option to use 'Existing Connections' as a data source for Power Pivot. Here we can select our carefully shaped & managed Power Query connections (e.g. to SQL Server, Web pages, flat files). However this interface only permits the selection of...

QlikView Resources for Beginner Developer

I am looking to add Qlikview Development to my skill-set. I have a C# and SQl background. Are there any free online resources to getting me going at developer level not end-user? What's the best starting place for me and the level of difficulty involved. I have seen the capabilities...

Rank Values of one column by filtering on 2nd column in DAX

I'm trying to use the RANKX formula rank the values of one column, but filtered for the value of a second column. In this example, col2 is a simple counter running in ascending value. I'm trying to find item_id's Rank value relative to the col1. col1 col2 1001 8001 1001...

Display Column Chart Y Axis Labels in Kilo(K), Million(M) in webi reports

Can we do formatting in Column chart value Axis Labels to show $1000000 as $1M, that is to replace 0's with M for million representation in Webi Reports? Please guide me if this is possible. Thanks Niki...

how to create Pivot query for this?

I need output like this. In this no limit of shape; for example : [RD],[PC],[EM],[SE],[PR],[CM] My data looks like this : POINTER_RANGE SHAPE_NAME PCS CTS VALUE 0.30-0.39 RD 6 1.826 3503.17 0.40-0.49 RD 3 1.238 2837.62 0.50-0.69 PR 1 0.504 776.16 0.50-0.69 RD 5 2.618 8639.58 0.70-0.89 PC 5 4.109...

Qlikview Automate past 12 months selection

I have a combo chart which shows the average days it took for a person to pay their bill. The Dimension of the Chart is = [Pay Month Year last 12 months] There are no Dimension limits There is 1 expression which is called Average and its definition is: avg({<...

Calculate the number of business days between two dates in power pivot

I am looking for a formula to calculate the number of weekdays/business days between two dates in power pivot. I do the same in T-SQL using the following query DATEDIFF(dd, Date1, GETDATE()) - (DATEDIFF(wk, Date1, GETDATE()) * 2) - CASE WHEN DATEPART(dw, Date1) = 1 THEN 1 ELSE 0 END...

How to link table row content to source in Power View

I am currently able to use Power View to view, filter, and highlight my data. However I haven't figured out a way to link my table rows to the data source (i.e. tables in other tabs of the Excel spreadsheet). so that if I double-click on a row, Excel will...

Can't open some sheet tabs within a qvw

In my QlikView document, when in the web view, i cant open some sheet tabs. Some open fine, others don't seem to have any link on them. These are the main tabs along the top and the properties are the same between one that works and one that doesn't....

Oracle Discoverer - Multiple Join On Single Table

I have two folders defined under business area from database tables called EMP and PROJECTS EMP table has the following columns emp_number emp_name projects table has the following columns proj_id proj_initiated_by proj_started_by proj_closed_by Joins are generated as emp.emp_number -> projects.proj_initiated_by (one_to_many) emp.emp_number -> projects.proj_started_by (one_to_many) emp.emp_number -> projects.proj_closed_by (one_to_many) In...

Aggregate Transformation vs Sort (remove Duplicate) in SSIS

I'm trying to populate dimension tables on a regular basis and I've thought of two ways of getting distinct values for my dimension: Using an Aggregate transformation and then using the "Group by" operation. Using a Sort transformation while removing duplicates. I'm not sure which one is better (more efficient),...

PowerPivot - How to filter columns yet retain grand total

in the table below, I would like to graph a subset of the columns, specifically the ones highlighted in green. however, I would like to compute the percentage based upon the grand total of the whole set. filtering columns rescopes the data set and hence the denominator. I would like...

DAX for MAX of a group

I have two data columns: Record | Record Version ------------------------ 1 | 1 1 | 2 1 | 3 2 | 1 2 | 2 2 | 3 2 | 4 3 | 1 3 | 2 3 | 3 4 | 1 4 | 2 4 | 3 4...

Pentaho - CSV Input not understanding special character [Windows to Linux]

I have a transformation on Pentaho Data Integration where the first thing I do is I use the "CSV Input" to map my flat file. I've never had a problem with it on windows, but now I'm chaning my server that spoon is going to run to a linux server...

How to select a particular number in a list box and make the other list box to only display the numbers greater than the selected number in QlikView?

I have two list boxes: Box1 and Box2. Box1 has values 10,20,30,40 etc and Box2 has the same 10,20,30 etc. After creating two separate list boxes for Box1 and Box2 respectively in the dashboard, how do I get to show only the values in Box2 which are greater than the...

Filtering Data In PowerPivot on Sharepoint

When viewing a PowerPivot workbook natively all is well. However when I save it to Sharepoint and view it via a browser, I find that the option to filter data by typing in a value is no longer present. (All there is is an extremely LONG list of values which...

How to make a calculated member in MDX for SSAS?

I have a problem in my calculation and can't solve. Example: Our company sells about 100 products, divided into categories and product ID. Product ID is the smallest granularity. I need to show how this product list we sell in each city. See the picture: Overall, without showing the category...

PowerPivot : Use Relative address for an Excel Connection

I am importing data from external excel file into PowerPivot (Using option "From External Sources -> Excel File". When I update data from in PowerPivot, it pulls data correctly from external excel file. Problem occurs when these two excel files (one having PowerPivot and another with source data) are deployed...

Kettle - Read CSV with comma as decimal mark

I have a transformation on Pentaho Data Integration (aka Kettle) where the first thing I do is I use the "CSV Input" to map my flat file. I've never had a problem with this step on windows, but now I'm chaning the server where spoon is going to run to...

Natural Key and Fact tables

I'm new on dimensional modelling I believe that you guys can help me in the following doubts. In the production system I have a transaction table, sales table for example.The unique identifier is a primary key called SaleId. Example: My doubt is when modelling the fact table should the SaleID...

Implement complex calculations with SQL or rather use ETL?

We are currently developing a web application for financial analysis using PHP (Zend Framework 2) and Doctrine as ORM-Tool (MySQL database). The complex financial calculations are done on the server-side, since the client has to be simple as possible. The data should be available in nearly "real-time", since the user...

How to construct a pivot table that contains values based on certain conditions

I want to construct a pivot table from a huge data-set. Now I want to construct the pivot table that consists of values based on certain specified conditions, for example, the entries with age>25 will only be populated in the pivot table. How to do that without manually filtering out...

Sort entire Pivot table in Excel 2013 by column

I have a Pivot Table with many columns. I want the Pivot the ability to sort one of the columns in a way that the whole column is sorted and not the relative position in the hierarchy. Example: NAME PRODUCT SUM Joe A 400 Joe B 200 Joe B 300...