Matrix shows all rows when adding a measure

[Anonymous]

Hi!

I have three tables in my model: Opportunities, goals and dates. Opportunities and goals have relationships with Date table:

a. Actual Close Date (Opportunities) - Day(Table)

b. GoalStartDate(Goals) - Day(Table)

Opportunities cloumns: Opp Id, Opp Number, Actual Close Date

Goals columns: GoalId, MetricGoalId, Target Amount, GoalStartDate

I created a measure to get the target Amount of a goals with specified MetricGoalId

Noke Target Amount = 
      if(
           max(goals[_metricid_value])="83fd18c0-5252-ec11-8f8e-000d3a0ce23b",
           CALCULATE(
               SUM(goals[targetmoney]),
               goals[_metricid_value]="83fd18c0-5252-ec11-8f8e-000d3a0ce23b"), 
           BLANK()
       )

I'm creating a matrix and it looks good. 

The problem is that when i add the measure my matrix shows all opportunities in each month.

There is a way to fix it? I think that my measure is not the correct one at all but i cannot understand what i need to change.

[Ashish_Mathur · Accepted solution]

Hi,

This is happening because the Opp ID column is not in the Goals Table.  Can you bring over that column in the Goals Table via a calculated column formnla.  To your visual, drag Opp ID from the Goals Table.  

[Anonymous]

Hi! Now i'm understand what it is happenig. I will make changes to my schema to get opp id in goals table. Thabk you

[Ashish_Mathur]

You are welcome.

[Anonymous]

Help please!

[Greg_Deckler]

@Anonymous Can't see what the measure is returning so not sure. You could always create a Complex Selector: (1) The Complex Selector - Microsoft Power BI Community

[Anonymous]

@Greg_Deckler this is what the measure is returning when the opp number is not addedd. All is ok

And when opp number is added