DAX - Counting Occurrence

Question

I have a simple list of OpportunityIDs in a PowerPivot data model like this:

OPPORTUNITY_ID    
1                   
2                
3                 
3                 
3                  
4                 
4                 
5                 

And I want to add a DAX expression in a CalculatedColumn that counts the occurrence of each OpportunityID, like this:

OPPORTUNITY_ID    COUNT
1                 1    
2                 1
3                 1
3                 2
3                 3     
4                 1
4                 2
5                 1

So, where there are multiple occurrences of the OpportunityID, the count would show cumulatively e.g. 1,2,3 etc.

I tried many ways of doing this but can't get anything to work. I think that answer may lie in the RANKX expression, but I don't understand how to get it working.

Any help will be greatly appreciated!

Answer 1

I couldn't figure this out with DAX so in the end I added this SQL line into the source data query

SELECT    
ROW_NUMBER() OVER(PARTITION BY co.[OpportunityID] ORDER BY co.[OpportunityID] DESC) AS Row

Answer 2

The proper measure for this in DAX is

DistinctOpportunities:=DISTINCTCOUNT([OpportunityID])

Answer 3

Try this: In DAX, the COUNTROWS function will count all the rows in the table passed to it. Using the FILTER function, create a subset of the OPPORTUNITYTABLE. The EARLIER function gives row context to a column argument; in this case invoking the current row in the table. So the following expression will return the count of rows in OPPORTUNITYTABLE where 'OPPORTUNITYTABLE'[OPPORTUNITY_ID] matches the current row.

=COUNTROWS(
    FILTER(
       'OPPORTUNITYTABLE',
       'OPPORTUNITYTABLE'[OPPORTUNITY_ID]=                    
          EARLIER(
             'OPPORTUNITYTABLE'[OPPORTUNITY_ID ])
    )
)

Answer 4

There is no DAX equivalent to the SQL ROW_NUMBER() window function. Without another field to deterministically break "ties" between identical rows you cannot achieve the same result.

That being said, it's very easy to sum only unique instances in DAX. The problem statement here is not "I need to get an equivalent to SQL's ROW_NUMBER()", but "I need to sum only unique occurences". The former is only a single way to help achieve the latter.

In DAX we can use SUMMARIZE() to group by arbitrary fields in a table. We can use SUMX() to iterate over an arbitrary table and accumulate into a sum some expression evaluated for each row.

Thus, if you have a table

Opportunity_ID  |  Opportunity_Value
     1                   100
     2                   150
     3                   200
     3                   200
     3                   200
     4                   75
     4                   75
     5                   300

Then we can use this measure to get a sum of only the distinct values for each Opportunity_ID

UniqueSum:=
SUMX(
    SUMMARIZE(
        FactOpportunity
        ,FactOpportunity[Opportunity_ID]
        ,FactOpportunity[Opportunity_Value]
    )
    ,FactOpportunity[Opportunity_Value]
)

Here we'd group by the named fields in FactOpportunity. The table defined by the SUMMARIZE() would look like the following:

Opportunity_ID  |  Opportunity_Value
     1                   100
     2                   150
     3                   200
     4                   75
     5                   300

The SUMX() would then iterate over each row in that result table and evaluate the value of [Opportunity_Value] on each row. Those would be accumulated into a sum, giving you a total of 825.