Forum Discussion

NilR's avatar
NilR
Post Patron
4 years ago
Solved

DAX Max Function with multiple conditions

Question to answer: If John in zip code group 0 between 202001 and 202004 has ever voted?

 

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 tireid but i get error "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)))

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

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    HI NilR,

    You can try to use follow measure formula to get the count of vote rows based on the current 'zip' and 'member' group of a specific year range:

    measure =
VAR voteCount =
    CALCULATE (
        COUNTROWS ( Table ),
        FILTER (
            ALLSELECTED ( Table ),
            [Vote] = "Y"
                && AND ( [INC Year] >= 202001, [INC Year] <= 202004 )
        ),
        VALUES ( Table[Zip_Code] ),
        VALUE ( Table[Member] )
    )
RETURN
    IF ( voteCount > 0, "Y", "N" )

    Regards,

    Xiaoxin Sheng

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    HI NilR,

    You can try to use follow measure formula to get the count of vote rows based on the current 'zip' and 'member' group of a specific year range:

    measure =
VAR voteCount =
    CALCULATE (
        COUNTROWS ( Table ),
        FILTER (
            ALLSELECTED ( Table ),
            [Vote] = "Y"
                && AND ( [INC Year] >= 202001, [INC Year] <= 202004 )
        ),
        VALUES ( Table[Zip_Code] ),
        VALUE ( Table[Member] )
    )
RETURN
    IF ( voteCount > 0, "Y", "N" )

    Regards,

    Xiaoxin Sheng

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    NilR , Assume you have selected a duration in slicer a measure can help

    Calculate(count(Table[Memeber]), filter(allselected(Table), Table[VOTE] ="Y" && Table[MEMBER] = max(Table[MEMBER])))

     

    vote in selected duration

    • NilR's avatar
      NilR
      Post Patron
      4 years ago

      Thank you! I need the Y and No result for another measure to use. this doesn't work for me.

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        NilR , Not very clear

         

        but this will give Y

        if(not(isblank( Calculate(count(Table[Memeber]), filter(allselected(Table), Table[VOTE] ="Y" && Table[MEMBER] = max(Table[MEMBER]))) )) , "Y", Blank())