Measure with static value causes relationship to explode

[kleigh]

For an export table, I need some columns with fixed values (same for each row). So I created a measure like

Date Requested = FORMAT(NOW(), "yyyy-mm-dd")

This worked just fine when I had the one table. When I added a second table, it cross-joined and added all possible rows from the second table. If I make the measure do a little more work, it works as expected:

Key Count = DISTINCTCOUNT('Table A'[Key])

This happens in a minimal testcase. 
 ...   ... 

 

Why is it expanding the join like this and how do I stop it?

[Sergii24 · Accepted solution]

Hello @kleigh, your relationship is defining the way of interaction between excisting pair of values (such as 1-A-AAA), but there are also those "non-existing" (such as 1-A-BBB). You don't see unexisting values as long as you don't call a measure to show them.

Let's take a look at your measure: 

Date Requested = FORMAT(NOW(), "yyyy-mm-dd")

As you can see, it has nothing to do with your data, so technically its result exists for any combination (including those "unexisting").

I've added other 2 measures to show you how you can control the desired result:

Combination exists = DISTINCTCOUNT( 'Table A'[Key] )

Combination doesn't exist = 
    IF(
        DISTINCTCOUNT( 'Table A'[Key] ) > 0,
        BLANK(),
        1
    )

By keeping on the visual only measure "Combination exists", you can get all existing and currently selected pairs (to see all pairs whatever filters applied, you can use ALL() ).

I hope it helps!

[kleigh]

Thanks, I was able to adapt this slightly:

Date Requested FIXED = SWITCH(DISTINCTCOUNT('Table A'[Key]),
BLANK(),BLANK(),
1, FORMAT(NOW(), "yyyy-mm-dd"),
"--")

This seems to be working, and gives a bonus check that it has been used in the correct context.