Forum Discussion
NilR
Post Patron
Post PatronIf value in this date range ever existed?
Question to answer: If John in zip code 0 between Date 202001 and 202004 has ever voted = "Y"? This is how I would right in sql but i need a measure in DAX to use the Y or N result in another ...
NilR try this
Measure = VAR _name = "John" VAR _lower = 202001 VAR _upper = 202004 VAR _vote = "Y" --VAR _zip = 0 VAR _count4 = CALCULATE ( COUNT ( Votes[Vote] ), Votes[INC_YEAR] >= _lower && Votes[INC_YEAR] <= _upper && Votes[Vote] = _vote --&& Votes[ZIP] = _zip ) // VAR _filt = // IF ( // MAX ( Votes[INC_YEAR] ) >= _lower // && MAX ( Votes[INC_YEAR] ) <= _upper // && MAX ( Votes[MEMBER] ) = _name, // --&& MAX ( Votes[ZIP] ) = _zip, // "Between" & " " & _lower & " " & "and" & " " & _upper & " " & _name & " " & "with zip" & " " & MAX ( Votes[ZIP] ) & " " & "has voted" & " " & _count4 & " " & "times" // ) VAR _test = IF ( MAX ( Votes[INC_YEAR] ) >= _lower && MAX ( Votes[INC_YEAR] ) <= _upper && MAX ( Votes[MEMBER] ) = _name, _count4 ) RETURN _test
NilRPost Patron
Post PatronThis is exactly what I needed, Thank you!
One quetion. I do not have the zip code filter but want to group by zipcode. how can I apply into your formula?
smpa01
Community Champion
Community ChampionNilR try this
Measure =
VAR _name = "John"
VAR _lower = 202001
VAR _upper = 202004
VAR _vote = "Y"
--VAR _zip = 0
VAR _count4 =
CALCULATE (
COUNT ( Votes[Vote] ),
Votes[INC_YEAR] >= _lower
&& Votes[INC_YEAR] <= _upper
&& Votes[Vote] = _vote
--&& Votes[ZIP] = _zip
)
// VAR _filt =
// IF (
// MAX ( Votes[INC_YEAR] ) >= _lower
// && MAX ( Votes[INC_YEAR] ) <= _upper
// && MAX ( Votes[MEMBER] ) = _name,
// --&& MAX ( Votes[ZIP] ) = _zip,
// "Between" & " " & _lower & " " & "and" & " " & _upper & " " & _name & " " & "with zip" & " " & MAX ( Votes[ZIP] ) & " " & "has voted" & " " & _count4 & " " & "times"
// )
VAR _test =
IF (
MAX ( Votes[INC_YEAR] ) >= _lower
&& MAX ( Votes[INC_YEAR] ) <= _upper
&& MAX ( Votes[MEMBER] ) = _name,
_count4
)
RETURN
_test