topic Re: Power BI DAX measure returns duplicates after selecting values from another table in DAX Commands and Tips

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

Anonymous — Mon, 24 Apr 2023 11:11:40 GMT

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

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

tamerj1 — Mon, 24 Apr 2023 10:32:01 GMT

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

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

Anonymous — Mon, 24 Apr 2023 11:13:02 GMT

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.

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

tamerj1 — Mon, 24 Apr 2023 11:50:05 GMT

Anonymous
You can add ProjectName as a measure

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

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

Anonymous — Mon, 24 Apr 2023 13:03:37 GMT

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?

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

tamerj1 — Mon, 24 Apr 2023 13:28:04 GMT

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

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

Anonymous — Mon, 24 Apr 2023 14:20:56 GMT

tamerj1 thank you so much!

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

OnofreBisbal — Wed, 20 Aug 2025 11:17:42 GMT

Hi!

I'm having the same problem. However, in my case is not efficient to create a measure for each column from another table because I have a lot of them. Do you think that is there any way to solve the problem editing the measure?