Fixed Value at Dimension Level

[Anonymous]

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

[Anonymous · Accepted solution]

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")))

[Anonymous]

Hi chirayuw,

Perfectly working...Thanks Heaps...!

Best Regards,

Ganesh