I've a loaded a simple dataset with two columns (start date and end date).
Now I've created a straight table with two columns: for first one I chosen a dimension type column and put "isnull([Start Date])" in its definition, for second one same thing but this time I've chosen a measure type column.
Could you kindly explain me why isNull function has a different behaviour between the two different column types ?
The simple answer is that IsNull() returns different results because you are evaluating different value in the two columns. I can not really understand the purpose of a test like this. What are you trying to accomplish?
The calculated dimension generates a array of values, which would typically be the values you season each row in the table. IsNull() is evaluated for each date value in this case, and generates a array of multiple zeros. Dimensions only display distinct value, so there will only be one row with one zero in the chart.
The measure is calculated over he dimension values. Over the zero value the data set will span over all date values. There is not one distinct date value in the current date field, so the reference to the field without an aggregation function return NULL as an indication to that a distinct value could not be resolved. IsNull() of NULL is most often true, so the measure result becomes true (-1).
Consider that your measure is equal to IsNull(Only([Start Date])). Only([Start Date]) always returns NULL when there referred expression has more than one distinct value. If you select one date, the chart in your example will return false(0) for the measure.
proposed test scope is to understand the behaviour of isNull() function...
That's because I'm trying to write down a table with a calculated dimension, that could show a date from another table column where its value is only between "start date" and "end date", or all values when neither start nor end date have been selected.
Hi Paolo, I think than the dimension is returning 0 because it's evaluated for each of the values in the dimension and as thare are values it's not null, so the IsNull() returns zero..
In the expression it returns true because it has many different values for the same row, so it ends returning null().
If you select a date both should return IsNull()=0; and if you include the expression in an aggregation funtion than returns only one of the different values it also will return false. (ie: Max([Start Date]))