Forum Discussion

NilR's avatar
NilR
Icon for Post Patron rankPost Patron
4 years ago
Solved

If 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 ...
DAX Measures
Need Help DAX
  • smpa01's avatar
    smpa01
    4 years ago

    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

     

     

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

NilR  if you have a data source like following

 

 

| ZIP | INC_YEAR | MEMBER | Vote |
|-----|----------|--------|------|
| 0   | 202001   | John   | N    |
| 0   | 202001   | John   | Y    |
| 0   | 202002   | John   | Y    |
| 0   | 202002   | John   | N    |
| 0   | 202003   | John   | N    |
| 0   | 202004   | John   | N    |
| 0   | 202004   | John   | N    |
| 0   | 202005   | John   | N    |
| 1   | 202001   | John   | Y    |
| 0   | 202001   | Wick   | Y    |

 

 

You can reach following like this 

 

_new = 
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" & " " & _zip & " " & "has voted" & " " & _count4 & " " & "times"
    )
RETURN
    _filt

 

 

 

 

NilR's avatar
NilR
Icon for Post Patron rankPost Patron
4 years ago

This 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's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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