Forum Discussion
Helper VFilter 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?
Hi Anonymous (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:
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.
7 Replies
- amitchandak
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-trend/ba-p/882970 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 .
In addition, what’s your expected result? If the below screenshot is the result you want?
Best Regards
Rena
ClintHi Anonymous (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:
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.
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
