Power BI DAX measure returns duplicates after selecting values from another table

[Anonymous]

My problem concerns a measure in PBI DAX with the goal to return the result of:

1 - "the row value of the column price" / "the row value of the column budget"

To achieve this, I wrote the following code:

[notworkingRevenue] = 1 - DIVIDE([Measure Price], [Measure Budget]))) The measures [Measure Budget] and [Measure Price] are simple sums of the regarding columns.

It works fine until I activate the measure and display columns from another table in the visual. It works as long as I display only columns and measures of table Contracts 

or columns of both tables and the measure mentioned above without the "1 - " in front of it, defined as: [workingRevenue] = DIVIDE([Measure Price], [Measure Budget]))) 

But creates duplicates as soon as I display columns from both tables and the measure [notworkingRevenue).

Link to the dataset 

[Anonymous]

@tamerj1 wow that works! Thank you :)) 1. do you have any idea why it doesn't work without  it tho? I don't really get, why the -1 one breaks the otherwise working code... 

2. And do you know a way. to not show the 4.7 mil in the line with no value for ProjectName?

3. With the given solution I can't filter for the ProjectName correct? Besides I have filters (like date filters, ), I didn't include in the test dataset I created to reproduce the error. Can I just include those into the ProjectName measure?

[tamerj1]

@Anonymous 
1. When you place columns from both dimension and fact tables together in a table visual, the engine creates a crossjoin producing all the possible combinations between the two tables. However, by default, power bi hides the rows that produces blank values of all the measures placed in the table visual. This will mostly keep only the existing combinations of the resulted crossjoin table.

The (-1) or even (+0) i.e. any constant added or subtracted cannot be blank therefore it will force the engine to show the value for all possible combinations. In your case  1 - DIVIDE ( BLANK ( ), BLANK ( ) )= 1 i.e. 100% will be displayed over each and every row of the crossjoin table.

2. Yes. See below screenshot

3. Yes you are right. See below screenshot

[Anonymous]

@tamerj1 thank you so much! 

[tamerj1]

Hi @Anonymous 
You can use WeTransfer, DropBox, OneDrive or any cloud service to share the the dowload link.

[Anonymous]

Hi @tamerj1 ,

thanks for your advise! I edited my post and inserted the link to the dataset. 

Pls tell me, if there are any problems with the links or questions with the dataset.

[tamerj1 · Accepted solution]

@Anonymous 
You can add ProjectName as a measure

ProjectName = 
MAXX ( 
    Contracts,
    RELATED ( Projects[ProjectName] )
)