Comma vs Asterisk Between Set Analysis Modifiers

[steverosebrook]

I'm trying to create a measure that is supposed to count "open" (i.e. active) client contracts.  Basically, I need to see if the client's account has assets in it for a given date (which is in the Assets table), and if so, sum up the Contract Counter field (in the Client table).

I tried using an intersection symbol (*), but that didn't work.  I finally figured out that simply using a comma between modifiers worked.  So now I'm confused about what the difference is between a comma and an asterisk - that's my question here.  I'm wondering if it has something to do with the fact that the two fields I'm using are in different tables?  

For reference, here are the two formulas I came up with (the first one is the one that works):

Sum({<Assets={">$(=0)"},[Assets As Of]={"$(=max([Timeframe End Date]))"}>}[Contract Counter])

Sum({<Assets={">$(=0)"}>*<[Assets As Of]={"$(=max([Timeframe End Date]))"}>}[Contract Counter])

Below is a screen shot of a table I created to test the results.  The formula with an asterisk is very close, but not quite correct.  Honestly, I don't even know how it's coming up with the number it's getting - I've looked at the component parts, and it doesn't make sense why it's counting certain client contracts - I just know it's wrong.

[sunny_talwar]

It's strange that the expressions are giving different outputs because like you I always thought that the two expressions can be used interchangeably. Having said that, have you tried adding Assets and Assets As Of as dimensions to see which of the two set analysis is the second expressions not adhering to? May be if you are able to share a sample, we can look at it as well.

[steverosebrook]

Here's a screen shot of a table with all the component parts in it.  In doing this, I realized that for the group of "false positives", most of them have nulls in the Assets As Of field.  I thought maybe that was the issue, but then I noticed that there are some records where the formula is returning a zero, and some where it's returning a 1.  So I'm back to wondering what's going on again!

[sunny_talwar]

Are you making selection in Assets or Assets As Of when you are taking these screenshots? Can you try this and see if this gives the same result as the other expression

Sum({<Assets = {">$(=0)"}, [Assets As Of]>*<[Assets As Of] = {"$(=max([Timeframe End Date]))"}, Assets>}[Contract Counter])

[sunny_talwar]

Also, I see you have Assets as an expression... may be Sum(Assets)... can you actually add Assets as a dimension....

[steverosebrook]

Now we're getting somewhere.  I switched to using Assets as a dimension, and now I'm able to see that in the cases where the asterisk formula is returning false positives, both the Assets value and the Assets As Of value are null.  So it appears that  these formulas are handling nulls differently.  (BTW, the formula you listed above returns the same results as mine that has the asterisk in it - i.e. it's not working correctly either.)

[sunny_talwar]

This is what I sort of imagined... but I don't know why it is doing this. May be @marcus_sommer, @rwunderlich, or @hic are able to help us out here.

Best,
Sunny

[marcus_sommer]

Usually both approaches are interchangeably but even if they return often the same results - they are not absolutely identically and like always it depends on the data and the requirements which approach is the right one or at least the most suitable.

Without going deeper and to test it in detail I think you have already found the cause of the differences which is a differently NULL handling. I think it's from effect a bit similar to the set analysis differences of -= against =- and because of the fact that NULL isn't stored in any way it couldn't be queried in a direct way else it needs a detour over other fields. Please take a look on the second and third example from here: Excluding-values-in-Set-Analysis

- Marcus