Forum Discussion

Clint's avatar
Clint
Helper V
6 years ago
Solved

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...
Need Help
  • Clint's avatar
    Clint
    6 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

Clint's avatar
Clint
Helper V
6 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Clint
      Helper V
      6 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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