This particular example returns only rows which are completely in date_from and date_end period.
At the end I need that when I choose vMinDate and vMaxDate in filter pane, table returns only those rows which is included in specific data range (date_from and date_end).
For example if date_from is 01.01.2023 and date_end is 30.01.2023 and I pick vMinDate = 10.01.2023 and vMaxDate = 15.01.2023 it will return all rows which are active in 10.01.2023 - 15.01.2023 based on date_from and date_end.
The specifics depend on your data model and the contents of the table, so it's hard to say.
What I often do in situations like these is generate a record for each day an ID was active. This may or may not be feasible for your situation. It typically looks like:
Load ID, date_from+IterNo() - 1 as [Active Date]
From YourTable
While date_from+IterNo() - 1 <= date_end;
You would then make a regular selection on the Active Date field - a range of dates or a single date as desired. Since this would filter down to only IDs active on that date, you will not have to write any complex formulas. This also has the advantage of reducing all of your other filters to the possible values for that range, whereas the variable approach does not do this.
This does impact the app size and load time, but the impact is often not enough to matter much, and the performance on the front end is typically better than when using assorted sets or if() statements.
Set analysis is typically applied to your *measures*, not your dimensions. Combined with hiding zero/null measures, this should result in returning only the values within the range.
Of course, ideally, you should simply make selections on the Date field and not use variables and From-To. That would be more efficient and result in simple expressions.
Thanks for the idea but it works the same as it worked for dimension.
The desired result is when I pick any date or date period using variables between row date_fromand date_end it returns that row. Let's say date_from and date_end are periods when rows id is "active". I need to get all "active" id in period I set in variable pane.
The specifics depend on your data model and the contents of the table, so it's hard to say.
What I often do in situations like these is generate a record for each day an ID was active. This may or may not be feasible for your situation. It typically looks like:
Load ID, date_from+IterNo() - 1 as [Active Date]
From YourTable
While date_from+IterNo() - 1 <= date_end;
You would then make a regular selection on the Active Date field - a range of dates or a single date as desired. Since this would filter down to only IDs active on that date, you will not have to write any complex formulas. This also has the advantage of reducing all of your other filters to the possible values for that range, whereas the variable approach does not do this.
This does impact the app size and load time, but the impact is often not enough to matter much, and the performance on the front end is typically better than when using assorted sets or if() statements.
It sounds a bit as if there no "real" date exists. If so I suggest to consider an appropriate resolving with an intervalmatch approach and then using period fields from calendar to select the wanted periods.
