Apparent set analysis inconsistency

[paolo_cordini]

Hello

In my Datamodel there is a calendar, linked to the fact table, as usual…:

Now, the measure in the table below has a set analysis which selects the dates above April 20^th

The field “DataMovSettimana” contains the week, it is calculated in the script as Week(DataMov).

As you can see, the current selection is DataMovSettimana=18.

Then, as expected, the result is the intersection of the set analysis and the current selections, thus showing only dates pertaining to week 18.

So, the result of the table below is the expected one.

Then, I remove the dimension “DataMovSettimana” from the table, without modifying anything in the selections, and the result is shown below:

As you can see, the measure total value is different as it apparently disregards the selection of the week. Moreover, the espression “week(DataMov)” is not calculated for the dates excluded in the set analysis.

Then, I remove one of the selections (not the week). Now the result is correct….

Moreover, how this can be explained?

In order to debug it, I made a copy of the date fields, both in the fact table and in the calendar table

This is the result. Does it make sense? Anyway, it is a workaround for me in order to get correct results.

I guess there is some datamodel issue but I am running out of ideas to throubleshoot it.

Any suggestion?

[Firefly_cam]

Check the format of DataMov in m-movimie table.
Seems that the relation key is not equal and you're calculating 22-28/04 dates based on the values in m-movimie table which are not related to calendar values.
When you select smth in Calendar, it works fine as those rows which are not related are disregarded.

Also as a thought, you might need to enclose your date value into single quotes in this case >='20/04/2024'  

[marcus_sommer]

There is no reason to apply a formatting to DataMov without specifying a different format as the default one. Therefore I assume that the date isn't a date else a timestamp which remains a timestamp regardless how it's formatted - even by date(DataMov, 'YYYY') it would be a timestamp and not a year.

If my above assumption is right, you will need something like this:

date(floor(DataMov))

[paolo_cordini]

I first thought the same thing, already tried date(floor()), same issue....

[Lech_Miszkiewicz]

I will say your calendar does not link to all records in your fact table. For test you can create duplicate of DataMov field in fact table DataMov as DataMovFact and DataMov as DataMovCal and then put all 3 fields DataMov, DataMovFact and DataMovCal in single table as diemension. If you see any gaps that is your answer. 

Cheers

[Firefly_cam]

Not sure if it's a timestamp issue, he's made copies of fields and both copies are not working:
 

In case of a timestamp issue at least the last sum() with DataMov_movime={} would work as expected

[marcus_sommer]

Make sure that's identically applied in the facts as well as within the calendar.

A chart with expressions isn't very helpful to check the exists and data-quality and also the associations between them. Better will be to use a table-box and set there all relevant fields side by side - here the date-key + the fact/dimension-date + the week + the other object-dimension and the measure-field. I assume something will look odd and could be investigated in more depths.

[paolo_cordini]

No, the two copies of the date fields are working. The '91.654'  value that you see is correct. The result is not the correct one if I use the key date field.

[paolo_cordini]

Already done. Nothing odd, everything appears correct with no misalignments, no NULLs, no holes...

[Firefly_cam]

Ok, Let's start from the beginning. The issue below is because you're trying to get Week(Date) in measure without aggregation. Measures return Null() when the result is a range instead of value. You can try Only(Week(Date)).

As for the second part about set analysis, I have one theory what is the issue and this not obvious.
Can you do a favour and get a table with 2 dimensions: DateMov and Num(DateMov)? I would imagine you'll have multiple values of Num(DateMov) for each DateMov