Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mwnance
Helper I
Helper I

Help -Convert Converted Column to measure

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.

 

1 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

 

MFelix_1-1661431773068.png

See the PBIX attach.

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

5 REPLIES 5
MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

 

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

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

 

MFelix_1-1661431773068.png

See the PBIX attach.

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors