Thanks for the quick response. This works perfectly fine when I only have one data source. Let's say if I had two data sources connected by Project ID can we still do the same thing ? I tried the formula in the view that I created using 2 data sources but I keep getting an error that says "Only Column Names are permitted in the dimensionality declaration"
I have attached the file with 2 data sources also.
Distinct .twbx 24.4 KB
This isn't going to solve your issue directly, but what you sort of want is the equivalent of a WINDOW_COUNTD that you can RESTART EVERY [Program]
There are past discussions of stuff like this. Here is one: How to implement a "WINDOW_COUNTD" - like calculation...
And at the bottom of that thread is a link to another thread that posts a workbook that actually hacks up a working model of simulating that sort of thing. I have never been able to make it work for my own uses because large sets of data make the solution horribly expensive. But if your data set is limited enough, it may work for you.
Sorry for the additional reply, but Joe's comment made me think of another option, and no blending is necessary. I was able to make this work with 2 different table calculations:
First, use the DENSE_RANK to get a rank for each resource:
resource rank = RANK_DENSE(MAX([Resource Code]),'asc')
apply that table calc as shown:
Next, create another table calc function using WINDOW_MAX:
max rank = WINDOW_MAX([resource rank])
and apply it as shown:
Your final result will be: