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 measure:

 

MAX(CASE MEMBER='JOHN' AND VOTE='Y' THEN 'Y' ELSE 'N')

 

 

this is what I've tireid IN dax but i get error and not sure if it's correct "Cannot convert value sd014 of type text to type true/false"

 

VAR _MAX= IF
(
	MAXX
	(FILTER('TBL',
        [MEMBER]= "JOHN" && [VOTE]="Y" && [DATE] >= MIN_DATE && [DATE]  <= _MAX_DATE),
         [ZIP]),
        "YES"
        , "NO")

VAR _VOTE_MBRS = CALCULATE(SUM('TBL'[MBRS]),FILTER('TBL',([DATE] >= MIN_DATE && [DATE]  <= MAX_DATE) && _MAX = "YES"))

VAR _MBRS = CALCULATE(SUM('TBL'[MBRS]),FILTER('TBL',([DATE] >= MIN_DATE && [DATE]  <= MAX_DATE) && _MAX = "NO"))

RETURN
SWITCH (
    SELECTEDVALUE ( 'TBL'[MBRS] ),
    "JOHN", _VOTE_MBRS ,
     _MBRS
)

 

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

     

     

7 Replies

  • bcdobbs's avatar
    bcdobbs
    Icon for Community Champion rankCommunity Champion
    4 years ago

     

    Try this

     

     

    TestExists = 

VAR tblYesVotes =
    CALCULATETABLE(
        SUMMARIZE(
            Votes,
            Votes[ZIP CODE],
            Votes[MEMBER]
        ),
        Votes[INC_YEAR] >= 202001 && Votes[INC_YEAR] <= 202004,
        Votes[VOTE] = "Y"
    )

RETURN IF( ISEMPTY( tblYesVotes ), "N", "Y")

     

     

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

      Thank you! I get an erro that 'tblYesVotes' is a table name and cannot be used to define a variable. 😞

      • bcdobbs's avatar
        bcdobbs
        Icon for Community Champion rankCommunity Champion
        4 years ago

        That's odd. You don't already have a table named that in your model? Try given the variable a different name?

         

        just to check, you are creating a measure?

  • 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