Forum Discussion

ahuhn's avatar
ahuhn
Icon for Advocate I rankAdvocate I
7 years ago
Solved

use parameters in dax

I made a DAX measure:

 

Measure = CALCULATE(COUNT(Sheet1[Status]),FILTER(Sheet1,[MonthUpdated]="201810" && [Status]="Alpha"))

 

I also created a parameter YYYYMM to represent [MonthUpdated], to permit the user to choose month of interest for Alpha projects.

 

Measure = CALCULATE(COUNT(Sheet1[Status]),FILTER(Sheet1,[MonthUpdated]=YYYYMM && [Status]="Alpha"))

 

2 issues:

 

1 - When I make a slicer for the parameter, only one option exists (ie 201809 or 201810, or whatever I set the default value on), and none of the other options are available

2 - When I reference the parameter in the DAX equation, the calculation is incorrect

 

Any suggestions?

 

 

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi ahuhn

     

    You can get the other count which is not selected by slicer with below measure. 

     

    Measure  =
    CALCULATE (
     MAX ( Sheet1[MonthUpdated] ),
     FILTER (
     Sheet1,
     Sheet1[MonthUpdated] <> SELECTEDVALUE ( Slicer[YYYYMM] )
     && Sheet1[Status] = "Alpha"
     )
    )

    Regards,

     

    Cherie

8 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi ahuhn

     

    You may create a slicer table as below. Then create a measure with SELECTEDVALUE Function to get the value as requested.

    Count =
CALCULATE (
    COUNT ( Sheet1[Status] ),
    FILTER (
        Sheet1,
        Sheet1[MonthUpdated] = SELECTEDVALUE ( Slicer[YYYYMM] )
            && Sheet1[Status] = "Alpha"
    )
)

    Regards,

    Cherie

    • ahuhn's avatar
      ahuhn
      Icon for Advocate I rankAdvocate I
      7 years ago

      Anonymous that is great, I love it!

       

      Just a follow up.

       

      I want to be able to subtract another month's value, also being chosen by the user. I tried your method, but after selecting the slicer to 201809, the second slicer is also subsetted, and thus has only one option.

       

      This is the reason I was trying to use parameters

       

      Any suggestions? Can you answer here, or shall I create a new thread?

      • v-cherch-msft's avatar
        v-cherch-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi ahuhn

         

        You can get the other count which is not selected by slicer with below measure. 

         

        Measure  =
        CALCULATE (
         MAX ( Sheet1[MonthUpdated] ),
         FILTER (
         Sheet1,
         Sheet1[MonthUpdated] <> SELECTEDVALUE ( Slicer[YYYYMM] )
         && Sheet1[Status] = "Alpha"
         )
        )

        Regards,

         

        Cherie

  • luxpbi's avatar
    luxpbi
    Icon for Helper V rankHelper V
    7 years ago

    Hi,

     

    Can you share sample dumy data and tell what are the results expected.

     

    Thank you

    • ahuhn's avatar
      ahuhn
      Icon for Advocate I rankAdvocate I
      7 years ago
      StatusMonthUpdated
      Alpha201810
      Alpha201810
      Beta201810
      Gamma201810
      Gamma201810
      Alpha201809

       

      When user selects parameter 201810, alpha result should be 2

      when user seelcts 201809, alpha result should be 1

       

      Unforunately, the parameters cannot be altered either

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        7 years ago

        ahuhn so if I understood correctly, you have a parameter which user uses to enter the month value and then you want to show count of status based on month value entered by users in parameter, correct?