maxifs in measure

[shei7141]

Hi everyone,

I have a table which looks like this:

Project No.	Phases	EstCost
1	SA	10
1	Option 1	15
1	Option 2	11
1	Option 3	15
1	PS	20
2	SA	5
2	Option1	6
2	Option2	4
2	Option3	7
2	PS	1

How can I write a measure which can return if the Phases value is not "*option* ", return the estCost as it is, if the Phases value is *option* return the max of estCost of option 1 to option 3 for that particular project. The measure should return something like this:

Project No.	Phases	EstCost	Measure
1	SA	10	10
1	Option1	15	15
1	Option2	11
1	Option3	15
1	PS	20	20
2	SA	5	5
2	Option1	6	7
2	Option2	4
2	Option3	7
2	PS	1	1

Thanks heaps in advance

Cheers,

Shei

[v-lili6-msft · Accepted solution]

hi  @shei7141 

You could try this logic:

Measure = 

var _firstoption=CALCULATE(MIN('Table'[Phases]),FILTER(ALLSELECTED('Table'),'Table'[Project No.]=MAX('Table'[Project No.])&&SEARCH("Option",'Table'[Phases],1,0)>0))
var _notoption=CALCULATE(SUM('Table'[EstCost]),FILTER('Table',SEARCH("Option",'Table'[Phases],1,0)=0))
var _option=CALCULATE(MAX('Table'[EstCost]),FILTER(ALLSELECTED('Table'),'Table'[Project No.]=MAX('Table'[Project No.])&&SEARCH("Option",'Table'[Phases],1,0)>0))
return
IF(SEARCH("Option",MAX('Table'[Phases]),1,0)=0,_notoption,IF(MAX('Table'[Phases])=_firstoption,_option))

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[lbendlin]

Measure = 
var p=selectedvalue(ProjectPhases[Project No.])
var ph = SELECTEDVALUE(ProjectPhases[Phases])
var m = CALCULATE(max(ProjectPhases[EstCost]),ALLSELECTED(ProjectPhases),ProjectPhases[Project No.]=p,CONTAINSSTRING(ProjectPhases[Phases],"Option"))
return switch(TRUE(),CONTAINSSTRING(ph,"Option1"),m,CONTAINSSTRING(ph,"Option"),BLANK(),sum(ProjectPhases[EstCost]))