Forum Discussion
Fixed Value at Dimension Level
Hi,
Here, I am struggling to calculate custom column based on another two dimensions.
In below example, I wanted to calculate the field called "Main Task Status", which has to pick up the value of Status at the each level of "Main Task", when Category is "IT".
And also, if the Category 'IT' is having two Sub Tasks with different Status then it should be 'Progress', other wise it should have same status.
| Main Task | Category | Sub Task | Status |
| TIC001 | IT | sub-001 | Completed |
| TIC001 | Services | sub-002 | Completed |
| TIC001 | Services | sub-003 | Progress |
| TIC001 | Hardware | sub-004 | To Do |
| TIC002 | IT | sub-005 | Progress |
| TIC002 | IT | sub-006 | Completed |
| TIC002 | Services | sub-007 | Progress |
| TIC002 | Hardware | sub-008 | Progress |
| TIC003 | IT | sub-009 | Progress |
| TIC003 | IT | sub-010 | Progress |
| TIC003 | Services | sub-011 | Completed |
| TIC003 | Hardware | sub-012 | To Do |
I am expecting below results in Main Task Status
| Main Task | Category | Sub Task | Status | Main Task Status |
| TIC001 | IT | sub-001 | Completed | Completed |
| Services | sub-002 | Completed | Completed | |
| Services | sub-003 | Progress | Completed | |
| Hardware | sub-004 | To Do | Completed | |
| TIC002 | IT | sub-005 | Progress | Progress |
| IT | sub-006 | Completed | Progress | |
| Services | sub-007 | Progress | Progress | |
| Hardware | sub-008 | Progress | Progress | |
| TIC003 | IT | sub-009 | To Do | To Do |
| IT | sub-010 | To Do | To Do | |
| Services | sub-011 | Completed | To Do | |
| Hardware | sub-012 | To Do | To Do |
Any help is much appreciated.
Thanks,
Ganesh
Is this what you want. Add as custom column
Main Status = VAR ITComp = COUNTROWS(FILTER(Table1,Table1[Main Task]=EARLIER(Table1[Main Task]) && Table1[Category]="IT" && Table1[Status]="Completed")) VAR ITProg = COUNTROWS(FILTER(Table1,Table1[Main Task]=EARLIER(Table1[Main Task]) && Table1[Category]="IT" && Table1[Status]="Progress")) VAR ITToDo = COUNTROWS(FILTER(Table1,Table1[Main Task]=EARLIER(Table1[Main Task]) && Table1[Category]="IT" && Table1[Status]="To Do")) VAR ITAll = COUNTROWS(FILTER(Table1,Table1[Main Task]=EARLIER(Table1[Main Task]) && Table1[Category]="IT")) Return IF(ITComp = ITAll,"Completed",IF(ITProg = ITAll,"Progress",IF(ITToDo = ITAll,"To Do","Progress")))
2 Replies
Is this what you want. Add as custom column
Main Status = VAR ITComp = COUNTROWS(FILTER(Table1,Table1[Main Task]=EARLIER(Table1[Main Task]) && Table1[Category]="IT" && Table1[Status]="Completed")) VAR ITProg = COUNTROWS(FILTER(Table1,Table1[Main Task]=EARLIER(Table1[Main Task]) && Table1[Category]="IT" && Table1[Status]="Progress")) VAR ITToDo = COUNTROWS(FILTER(Table1,Table1[Main Task]=EARLIER(Table1[Main Task]) && Table1[Category]="IT" && Table1[Status]="To Do")) VAR ITAll = COUNTROWS(FILTER(Table1,Table1[Main Task]=EARLIER(Table1[Main Task]) && Table1[Category]="IT")) Return IF(ITComp = ITAll,"Completed",IF(ITProg = ITAll,"Progress",IF(ITToDo = ITAll,"To Do","Progress")))