Using a parameter in a filter statement in calculated column

[chipchidster]

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?

[v-yangliu-msft · Accepted solution]

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.

[bhanu_gautam]

@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]

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