Forum Discussion

chipchidster's avatar
chipchidster
Resolver I
1 year ago
Solved

Using a parameter in a filter statement in calculated column

I am trying to use a parameter within a filter statement, but no matter what I try I just cannot get it to work.  I'm clearly doing something wrong, but would be grateful if someone can point out what!

 

var planned = SUMX(FILTER(sprint_status_summary,
                          sprint_status_summary[sprint_id] = sprint_summary[sprint_id]
                       && sprint_status_summary[project_key] = sprint_summary[project_key]
                       && sprint_status_summary[start_status] IN VALUES(paramUnreleasedStatus)),
                    // && CONTAINSROW({"Ready For Live","Release Test"}, sprint_status_summary[start_status])),
                    //&& IF(CONTAINSROW(paramUnreleasedStatus, sprint_status_summary[start_status]),TRUE(),FALSE())),
                   sprint_status_summary[story_points])
In the DAX above, you can see the three approaches I have tried.  The first commented out line works (where I explicitly add the contents of paramUnreleasedStatus as a list).  However, if i try and reference the parameter instead (either using direct or using the values function first) the dax returns nothing (the condition of checking the param fails so the sum is always 0).
 
The param is set up as a text list and contains "Ready For Live","Release Test".  If I return the param instead of the calc in the column, i get the list of values correctly.
 
Any thoughts on what i am doing wrong?
"DAX " "DAX help" 'Need help'
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from bhanu_gautam , please allow me to provide another insight: 
    Hi  chipchidster ,

     

    Is the paramUnreleasedStatus you are referring to the Manage Parameters created in the Power Query?

    Check the parameter - Enable load - load to table

    You can try the following dax:

    Test =
SUMX(
    FILTER(ALL('sprint_status_summary'),
   'sprint_status_summary'[sprint_id]=EARLIER('sprint_status_summary'[sprint_id])&&
   'sprint_status_summary'[start_status] in
    SELECTCOLUMNS('paramUnreleasedStatus',"Ready For Live",[paramUnreleasedStatus])),[story_points])

    If the results above don't match your expected results, you can express your expected results in the form of a picture and we can help you better!

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    chipchidster , Try using

    dax
    var planned = SUMX(
    FILTER(
    sprint_status_summary,
    sprint_status_summary[sprint_id] = sprint_summary[sprint_id]
    && sprint_status_summary[project_key] = sprint_summary[project_key]
    && sprint_status_summary[start_status] IN VALUES(paramUnreleasedStatus)
    ),
    sprint_status_summary[story_points]
    )

    • chipchidster's avatar
      chipchidster
      Resolver I
      1 year ago

      Hi Bhanu - I am already trying that logic on line 4 of the example

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply from bhanu_gautam , please allow me to provide another insight: 
    Hi  chipchidster ,

     

    Is the paramUnreleasedStatus you are referring to the Manage Parameters created in the Power Query?

    Check the parameter - Enable load - load to table

    You can try the following dax:

    Test =
SUMX(
    FILTER(ALL('sprint_status_summary'),
   'sprint_status_summary'[sprint_id]=EARLIER('sprint_status_summary'[sprint_id])&&
   'sprint_status_summary'[start_status] in
    SELECTCOLUMNS('paramUnreleasedStatus',"Ready For Live",[paramUnreleasedStatus])),[story_points])

    If the results above don't match your expected results, you can express your expected results in the form of a picture and we can help you better!

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.