Forum Discussion
If statement in calculate Measure
Hi, looking for some help with a measure. I have a measure that sums a Value based on criteria filters in the Calculate statement. I would like the measure to calculate differently (to drop one of the criteria filters) based on one of the page slicers selection. The syntax that works fine is:
FH_VALUE = Calculate(sum(Funnel[Value]),Funnel[HalfInclusion]=1,Funnel[Forecast]="Commit",'Product'[System]="Sample",Funnel[HubSpoke]="No")
There is a slicer on the page for [Segment]. In case Segment="EA", then I want the measure to drop the [System] filter:
Calculate(sum(Funnel[Value]),Funnel[HalfInclusion]=1,Funnel[Forecast]="Commit",Funnel[HubSpoke]="No")
However, I can't use a normal IF statement because the [Segment] occurs as a column and is not aggregated.
Thanks in advance for any help or better ideas you may have!
Hi lmondavi
Does something like this help?
FH_VALUE = VAR IsEASelected = IF(ISFILTERED('Product'[Segment]) && MAX('Product'[Segment]) = "EA" , True,False) VAR Calc1 = Calculate(SUM(Funnel[Value]),Funnel[HalfInclusion]=1,Funnel[Forecast]="Commit",'Product'[System]="Sample",Funnel[HubSpoke]="No") VAR Calc2 = Calculate(SUM(Funnel[Value]),Funnel[HalfInclusion]=1,Funnel[Forecast]="Commit",Funnel[HubSpoke]="No") RETURN IF(IsEASelected,Calc1,Calc2)
3 Replies
- Phil_Seamark
Microsoft Employee
Hi lmondavi
Does something like this help?
FH_VALUE = VAR IsEASelected = IF(ISFILTERED('Product'[Segment]) && MAX('Product'[Segment]) = "EA" , True,False) VAR Calc1 = Calculate(SUM(Funnel[Value]),Funnel[HalfInclusion]=1,Funnel[Forecast]="Commit",'Product'[System]="Sample",Funnel[HubSpoke]="No") VAR Calc2 = Calculate(SUM(Funnel[Value]),Funnel[HalfInclusion]=1,Funnel[Forecast]="Commit",Funnel[HubSpoke]="No") RETURN IF(IsEASelected,Calc1,Calc2)Thank you, worked like a charm!!!
