Help -Convert Converted Column to measure

[mwnance]

I have this  Calculated Column that I use in a Stacked Column chart.   Works great but it does not react to the Slicer I have.

COUNTROWS(FILTER (ALLSELECTED(AllWorkItems),

                                         AllWorkItems[Work Item Type] = "User Story" &&

                                         AllWorkItems[Is Current] = TRUE()  &&

                                         AllWorkItems[Parent Work Item Id] = 'Features Table'[FeatureID] &&

                                         AllWorkItems[State] in { "CLOSED" , "ACCEPTED", "RESOLVED"} ))

I need to convert it to a measure.  If I just copy and paste into a measure  I get the error 

"A single Value for column FeatureId in Table Feature Table cannot be determined.

That makes sense but I dont know how to get all the stories for that specific Feature ID.   I have  the AllWorkItems table which has all the items that can be a User Story or a Feature..  The user Stories have a Parent ID which will be the Feature ID.

Any help would be appreciated.

[MFelix]

Hi @mwnance ,

Try the following code:

ddd =
COUNTROWS (
    FILTER (
        ALLSELECTED ( AllWorkItems ),
        AllWorkItems[Work Item Type] = "User Story"
            && AllWorkItems[Is Current] = TRUE ()
            && AllWorkItems[Parent Work Item Id]
                in Values ( 'Features Table'[FeatureID] )
            && AllWorkItems[State] IN { "CLOSED", "ACCEPTED", "RESOLVED" }
    )
)

Be aware that this may need adjustments, and depends on the context you are using in the visualization.

[mwnance]

Thanks for the reply..  I am not getting any results.. but dont get an error.   

The FeatureID is the X Axis of the Stacked Column chart  So I am trying to get the count for each column..ie each Feature ID  I have

[MFelix]

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[mwnance]

AllWorkItems will look something like this

ID     Story type     State     Parent Id     Is Current

1       Feature          OPEN       0                  Yes

2      User Story       Open       1                  Yes

3      User Story       Closed     1                  Yes

4       Feature          OPEN       0                  Yes

5      User Story       Open       1                  Yes

6      User Story       Closed     1                  Yes

I created the Feature Table ONLY because I wanted to get a list of Features in my Stacked Column chart then  Count all the User stories per feature by status

So I created a new table of all the Features and did Calculated Columns counting the stories by state.

[MFelix · Accepted solution]

Hi @mwnance ,

Based on what you wrote, you have the Feature table just to calculate the count of the values, ytou have two options with a basic measure.

Create the following measure:

Count of activities = COUNTROWS(FILTER (AllWorkItems,
                                         AllWorkItems[Story Type] = "User" &&
                                         AllWorkItems[Is Current] = TRUE()  &&
                                         AllWorkItems[State] in { "CLOSED" , "ACCEPTED", "RESOLVED"} )) 

Be aware that I needed to adjust the code for the sample you send out to me now you can:

1. Keep the Features table, do a relationship between both table by (One to many : FeatureID -> ParentID) and use the X-axis from the Feature ID
2. Delete the Features table and use the Parent ID from the original table has your x-axis has you can see result is equal on any option

See the PBIX attach.