Question:
I’m having some trouble with the logic behind a report and was hoping to get some help on how to capture more rows in a table with a date slicer.I’ll start by laying out the structure of my data and what I’m hoping to accomplish
I have a date column that I generated in SQL server. The whole report has to use direct query, so that would be one stipulation for possible solutions.
The date column just has dates like 1/1/2022, 1/2/2022, etc.
| DATE |
|---|
| 1 / 1 / 2022 |
| 1 / 2 / 2022 |
| 1 / 3 / 2022 |
| 1 / 4 / 2022 |
The data table is being filtered by a date slicer that uses the dates in the date column, as well as a DAX formula that gets selected values from then slicer.
Here is an example of the data table:
| Begin Date | End Date | Data |
|---|---|---|
| 2 / 30 / 2022 | 4 / 4 / 2022 | 0 |
| 3 / 6 / 2022 | 4 / 26 / 2022 | 0 |
| 4 / 7 / 2022 | 4 / 26 / 2022 | 0 |
| 4 / 30 / 2022 | 5 / 15 / 2022 | 0 |
In this instance, the table I’m filtering has two date columns that I need to filter by, hence the date table. For the data to appear, I’m filtering the slicer with 4-1-2022 to 4-30-2022. Ideally, If at least one date between Begin Date and End Date appears between the range given in the slicer with the date table, the row will appear.
Here is the code for what I have right now:
A problem occurs when I filter by a smaller date range, and I think it’s because my code is not picking up as many values as I want it to.
For instance, if I filter the Date column down to 4/1/2022 to 4/2/2022, I return these possible rows:
| Begin Date | End Date | Data |
|---|---|---|
| 4 / 1 / 2022 | 5 / 13 / 2022 | 0 |
| 4 / 1 / 2022 | 4 / 26 / 2022 | 0 |
| 4 / 1 / 2022 | 4 / 26 / 2022 | 0 |
| 4 / 1 / 2022 | 5 / 15 / 2022 | 0 |
But if I expand the filter by a day (4/1/2022 to 4/3/2022) I get a new row:
| Begin Date | End Date | Data |
|---|---|---|
| 3 / 20 / 2022 | 4 / 3 / 2022 | < New row |
| 4 / 1 / 2022 | 5 / 13 / 2022 | 0 |
| 4 / 1 / 2022 | 4 / 26 / 2022 | 0 |
| 4 / 1 / 2022 | 4 / 26 / 2022 | 0 |
| 4 / 1 / 2022 | 5 / 15 / 2022 | 0 |
Ideally, the filter range of 4/1/2022 to 4/2/2022 would have picked up this row already, because it contains 4/2/2022 between it’s begin and end date.
I think this is because my code is treating the selected dates as one value, where ideally they would look at all dates between the Begin Date and End Date columns, and see if any one of those dates exist within the specified date range, however my attempts at solving this with DAX have not worked to fix this issue.
Please let me know if there is a way to rewrite or remake my report or DAX to better accomplish the goal of displaying the correct rows. Let me know if I can clarify anything, ad I’ll try to answer as best I can (without showing the report)
Thank you!
Answer:
Can you try this measure?
COUNTX,NATURALINNERJOIN,DATESBETWEEN,CALCULATE,MAX,ALLSELECTED are supported in DQ.If you have better answer, please add a comment about this, thank you!