Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |