Forum Discussion
Helper IWhat is wrong with this formula?
HI Community!
I am trying to create a measure that compares 2 dates together to determine whether it should be flagged as a 0 or 1.
you're trying to reference a column in a measure, but you're doing it in a row context-dependent way that doesn't work properly in a measure (which evaluates in filter context).
Use a Calculated Column if you need row-by-row evaluation
IncludeDate =
IF(
NOT ISBLANK('YourTable'[EDC Go-Live]),
IF('YourTable'[EDC Go-Live] >= 'YourTable'[First Date to Include], 1, 0),
BLANK()
)
Use a Measure only if aggregating (e.g., MIN, MAX, FIRSTDATE)
IncludeDateMeasure =
VAR EDCDate = SELECTEDVALUE('YourTable'[EDC Go-Live])
VAR FirstDate = SELECTEDVALUE('YourTable'[First Date to Include])
RETURN
IF(
NOT ISBLANK(EDCDate),
IF(EDCDate >= FirstDate, 1, 0),
BLANK()
)
This works only if a single value is in context (e.g., when filtering by row in a visual or drillthrough).
5 Replies
Shravan133Super User
you're trying to reference a column in a measure, but you're doing it in a row context-dependent way that doesn't work properly in a measure (which evaluates in filter context).
Use a Calculated Column if you need row-by-row evaluation
IncludeDate =
IF(
NOT ISBLANK('YourTable'[EDC Go-Live]),
IF('YourTable'[EDC Go-Live] >= 'YourTable'[First Date to Include], 1, 0),
BLANK()
)
Use a Measure only if aggregating (e.g., MIN, MAX, FIRSTDATE)
IncludeDateMeasure =
VAR EDCDate = SELECTEDVALUE('YourTable'[EDC Go-Live])
VAR FirstDate = SELECTEDVALUE('YourTable'[First Date to Include])
RETURN
IF(
NOT ISBLANK(EDCDate),
IF(EDCDate >= FirstDate, 1, 0),
BLANK()
)
This works only if a single value is in context (e.g., when filtering by row in a visual or drillthrough).
Ashish_ExcelSolution Supplier
Hi,
This calculated column formula should work
Test = if(and([EDC Go-Live]>0,[EDC Go-Live]>[First Date to Include]),1,0)
Hope this helps.
- pankajnamekar25
Hello colettb
try this
This fails because column references like [EDC Go-Live] without an aggregation inside a measure don't return a value outside of filter context.
Use this
IncludeDate =
IF(
NOT ISBLANK('YourTable'[EDC Go-Live]) &&
'YourTable'[EDC Go-Live] >= 'YourTable'[First Date to Include],
1,
0
)
If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
colettbThank you! I appreciate your great explanation!
v-sgandrathiCommunity Support
Hi colettb,
Thank you for your question, and thank you to our community members for their precise guidance.
As mentioned, the issue arises from using row-level column references within a measure, which operates in a filter context and does not evaluate row-by-row. For such logic, comparing values from two columns on the same row a calculated column is the appropriate solution.
Also, if your visuals ensure a single row context, you can use SELECTEDVALUE in a measure. Please try the suggested calculated column formula, and let us know if you need assistance applying it or adapting it to your specific report. If one of the responses resolved your issue,
please mark it as the accepted answer to assist others in the community. We are here to provide further assistance if needed.Thank you.
