Forum Discussion
Measure with static value causes relationship to explode
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?
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!
2 Replies
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!
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.
