DAX Measure causes Cartesian product in table

[Anonymous]

Hi all,

I'm having trouble understanding why the following happens (and how to prevent it):

I have two tables, which I join via a 1:1 relation on a key.

 

Joining the two tablesth

The result after joining is as expected:

Result after joining

Now, when we add a measure: Ones = 1 and add this to the table we get a cartesian product of all project names, and a 1 on each row. Does anyone understand why this happens? 

Cartesian product result

Thanks! 

-Nils

[Anonymous]

Hey Tamerj1, 

Thanks for replying! 

If that's the case, how would I then prevent this from happening? Is there any way to detect that it's a non-existing combination (or is there a Power BI setting that let's me always hide non-existing combinations?)

[tamerj1]

@Anonymous 

You can use

IF ( NOT ISEMPTY ( TableName ), 1 )

[chuckbo]

Can I ask a follow-up question about your solution, please?

What is TableName?

[tamerj1]

@chuckbo 

The name of you fact table

[tamerj1 · Accepted solution]

@Anonymous 

When you add columns from multiple tables to a visual, Power Bi creates a crossjoin of these columns producing all possible combinations but hideing the non-existing combinations. By returning  a constat as a value you are forcing it to show all the combinations even if they don't exist.