FAQ Database Discussion Community


DAX for MAX of a group

visual-studio-2012,max,powerpivot,calculated-columns,dax
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...

How to link table row content to source in Power View

excel,powerpivot,powerview,powerbi,powerquery
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...

how to create Pivot query for this?

sql,sql-server,sql-server-2008,pivot-table,powerpivot
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...

PowerPivot : Use Relative address for an Excel Connection

excel,powerpivot
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...

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

excel,excel-2010,powerpivot,powerquery,powerbi
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...

How to create nested tiles in Power View

excel,powerpivot,powerview,powerbi,powerquery
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...

Microsoft Power BI Designer data model to Excel or PowerPivot

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

Sort entire Pivot table in Excel 2013 by column

sorting,excel-2010,pivot-table,excel-2013,powerpivot
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...

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

excel,vba,pivot-table,powerpivot
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...

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

powerpivot,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...

Filtering Data In PowerPivot on Sharepoint

excel,sharepoint,powerpivot
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...

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

powerpivot,dax,powerbi,powerquery
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...

many to many powerpivot relationship

many-to-many,relationship,powerpivot,powerview
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...

PowerPivot % Difference from previous month

excel,date,pivot-table,powerpivot
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...

How to create Power View report tiled by multiple KPIs?

excel,powerpivot,dax,powerview,kpi
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...

Calculating PERCENTILE in DAX - SOLVED

olap,powerpivot,dax,powerbi,ssas-tabular
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...

Top 5 and Bottom 5 records in Power Pivot

powerpivot,dax,powerview,calculated-measure
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...

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

excel,powerpivot,dax,bism
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...

PowerPivot - How to filter columns yet retain grand total

powerpivot
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...

Pivot Table with multiple rows all having the same level hierarchy

excel,reporting,pivot-table,powerpivot
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...

PowerBI hierarchy creation without the cloud

excel,hierarchy,powerpivot,powerview,powerbi
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...