FAQ Database Discussion Community


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

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

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

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

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

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

Calculate renewal date DAX

datetime,time,dax
I have a datetime column (starting_date) in a PowerPivot model. I would like to calculate a field showing how many months until next renewal date. Each year a renewal is coming up? Does somebody have an idea on how to do that dynamically in DAX?...

Identifying Context Filter in DAX Measure

excel-2013,dax
How do I create a DAX measure in Power Pivot that shows the column name that is used as the lowest level filter context for each row in the Pivot Table? I have 2 tables which are related on the Account column. Table DimAccount Account,Model_L1,Model_L2,Model_L3,Business_L1,Business_L2,Business_L3 1,Working Capital,Trade Payables,Trade Creditors,Opex,Technology,Managed Services...