cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Clint Member
Member

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?

5 REPLIES 5
Super User IV
Super User IV

Re: Filter issue w/Measure

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...





Did I answer your question? Mark my post as a solution!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Microsoft yingyinr
Microsoft

Re: Filter issue w/Measure

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

Clint Member
Member

Re: Filter issue w/Measure

Hi @yingyinr (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

Microsoft yingyinr
Microsoft

Re: Filter issue w/Measure

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

Clint Member
Member

Re: Filter issue w/Measure

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.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors