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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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