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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Clint
Helper V
Helper V

Filter issue w/Measure

Hey All,

Hoping someone can give me some insight into what I"m doing wrong w/this measure:

FY20 Velocity Target V2 =

CALCULATE(SUMX('TeamTarget','TeamTarget'[Target]),

ALLEXCEPT(SprintCompletion,SprintCompletion[Team])

)

I have two tables here: Team Target and Sprint Completion. There is a 1-* from Team Target to Sprint Completion. Single direction filter. What I am hoping this measure returns is the Target value for said team ie. 78 or 120 etc.... Target being a column in the Team Target table...

what it is returning instead is the sum value of all of the target values. So, I'm obviously doing something wrong in getting it to filter by Team. I think I need to use Related or Related Table but I'm not able to make those work. Any insights into what I'm doing wrong here?

1 ACCEPTED SOLUTION

Hi @v-yiruan-msft (Rena),

 

The table Team Target exists in a 1-* relationship w/the SprintCompletion table and the join is on the column Team name.  The filter is a one way filter from Team Target to Sprint Completion.  I cannot make this bi-directional because that would introduce complications with other tables (according to BI).  The incorrect results look like this:
Incorrect Team Stats.PNG

The number 996 represents the sum of all of the Team's target values which implies I'm not filtering correctly.

Since I needed this fixed right away, I forced the issue by creating a reference dupe of Sprint Completion (w/only the columns I really need) and related it to Team Target and also to the original Sprint Completion.  I was then able to set Team Target and the new Sprint Completion Table in a bi directional filter relationship.  I now get the expected results using the Team name from the new Sprint Completion table.

Correct Team Stats.PNG

View solution in original post

7 REPLIES 7
v-yiruan-msft
Community Support
Community Support

Hi @Clint,

In order to find the cause of the problem as soon as possible , could you please provide the data model structure and some sample data exclude sensitive data (as shown in the below screenshot ) ? And it would be better to provide your PBIX file if it is convenient .

sample data.JPG

In addition, what’s your expected result? If the below screenshot is the result you want?

 expected result.JPG

Best Regards

Rena

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

Hi @v-yiruan-msft (Rena),

 

The table Team Target exists in a 1-* relationship w/the SprintCompletion table and the join is on the column Team name.  The filter is a one way filter from Team Target to Sprint Completion.  I cannot make this bi-directional because that would introduce complications with other tables (according to BI).  The incorrect results look like this:
Incorrect Team Stats.PNG

The number 996 represents the sum of all of the Team's target values which implies I'm not filtering correctly.

Since I needed this fixed right away, I forced the issue by creating a reference dupe of Sprint Completion (w/only the columns I really need) and related it to Team Target and also to the original Sprint Completion.  I was then able to set Team Target and the new Sprint Completion Table in a bi directional filter relationship.  I now get the expected results using the Team name from the new Sprint Completion table.

Correct Team Stats.PNG

Hi @Clint

Thank you for your reply . So you solved your problem by creating a copy of the table Sprint completion and create the relationship with table Team Target as a workaround . That is really a good idea...

 

Best Regards

Rena

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

Thanks Rena.  The only downside I can see is it gave me a many to many between the two sprint tables but I think I can remove dupes from the reference copy w/o causing a problem.

Hi @Clint ,

Could you please mark your last reply as solution? It will help others if they face the similar problem with you. Thank you.

 

Best Regards

Rena

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

Done.  Thank you for your help.

amitchandak
Super User
Super User

Can you share sample data and sample output. If possible please share a sample pbix file after removing sensitive information.Thanks.
Proud to be a Datanaut My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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