Forum Discussion

Manar's avatar
Manar
Icon for Helper II rankHelper II
8 years ago
Solved

Pass multiple values from slicer into dax measure

Hi,

I am trying to filter the count of submissions by year and months selected by user (slicer),

I created a measure to get the count by year but in case of months, the end user can select multiple values (Jan, feb etc)

 

I wrote the following formula, but it only works when selecting one month not multiple

 

Submissions_CurrentyearValue =

 

                     var  YearValue= MAX(DimDateSubmission[Year])              

                    var MonthValue= ALLSELECTED('DimDateSubmission'[Month])                                                                     

 

                Return                                                                                                                    

                            Calculate(CountA(Submisions[Submission Number]), 

                                Filter (

 

                                 All(Submisions),  YEAR(Submisions[Submission Date]) = YearValue   && Month( Submisions[Submission Date]) =  MonthValue                                                          )                                                                                                                                                                               )

 

 

Is there any way to allow multiple selections on month slicer??

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hey Manar

     

    I think replacing the "=" with "IN" will solve your problem, so instead of

     

    && Month( Submisions[Submission Date]) =  MonthValue 

    you might want to try

     

    && Month( Submisions[Submission Date]) IN  MonthValue 

    Let me know if that solves your problem.

     

    Hope this helps,

    Parker

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hey Manar

     

    I think replacing the "=" with "IN" will solve your problem, so instead of

     

    && Month( Submisions[Submission Date]) =  MonthValue 

    you might want to try

     

    && Month( Submisions[Submission Date]) IN  MonthValue 

    Let me know if that solves your problem.

     

    Hope this helps,

    Parker

    • Manar's avatar
      Manar
      Icon for Helper II rankHelper II
      8 years ago

      Anonymous that worked!! thanks!

    • Gustavo_C's avatar
      Gustavo_C
      New Member
      6 years ago

      Hi all,

      I have a similar issue, but instead I need to get the month with the MAX and MIN net sales from current year per selected Status type. The user can choose among 3 categories (individually or clicking 2 or all 3 of them). If 2 or 3 categories together are chosen, the MAX and MIN must give the respective resulting month of the combined sales from these categories. The current measure looks like this:

       

      var Status=ALLSELECTED(Status[Status type])

      return

      calculate(sum(Orders[Amount]);filter((Orders);Orders[Status type] in Status ))

       

      Unfortunately, this measure provides inside a Card the total cumulated sales of the selected categories (for combined categories or individually). By adding this measure into a Matrix, I can see the period breakdown.

       

      However, if I replace SUM by MAX (see code below) I get always the maximum period BUT from one of the 3 categories. For instance, if the user selects the 3 categories together, the measure returns the max sales period from ONLY that category with the "highest max" among the 3 selected categories. This is wrong, because we expect to get the max sales period from the combined selected categories.

       

      calculate(max(Orders[Amount])

       

      Replacing SUM by MIN we get exactly the opposite result. The measure returns the min sales period from ONLY that category with the "lowest min" among the 3 selected categories, and not from the combined categories.

       

      calculate(min(Orders[Amount])

       

      I tried also to use SUMX, MAXX and MINX, the the result is the same.

      What am I doing wrong?

       

      Thank you very much in advance,

      Gustavo