Adding a date column to a combination table

To make your matrix respond to a date slicer, you need a measure that calculates the ticket count based on the filters currently applied to your report, including the date range.

1. Keep Your Combinations Separate

Instead of trying to embed the date into your Ticket_Every_Combination table, use it to define the rows and columns of your matrix. If Tickets[System] and Tickets[Project] already give you all the combinations you need, you might not even need the Ticket_Every_Combination table itself in your visual. You can just drag those columns directly into your matrix.

However, if you want a complete list of every possible combination even if no tickets exist for them, your current Ticket_Every_Combination calculated table is useful:

Ticket_Every_Combination =
VAR Systems = VALUES(Tickets[System])
VAR Project = VALUES(Tickets[Project])
RETURN
CROSSJOIN(Systems, Project)

This table simply lists all unique System-Project pairs. You'd use columns from this table in your matrix rows/columns.

2. Establish Your Date Foundation

For proper date filtering, especially if you plan on more complex time intelligence later, always use a Date table.

• Create a Date Table: If you don't have one, create a calculated table for dates.

   

  DateTable = CALENDARAUTO() // This automatically generates dates from your data model

  Go to the Model view, select your DateTable, and in the Properties pane, mark it as a Date table.

• Create a Relationship: In the Model view, drag the Date column from DateTable to the Created_Date column in your Tickets table. This creates a one-to-many relationship (one date in DateTable can have many tickets). Ensure this relationship is active.

3. Create the Dynamic Count Measure

This is the core of the solution. This measure will count tickets based on the Project, System, and any applied date filters.

Count of Tickets by Combination =
COUNTROWS(
    FILTER(
        ALLSELECTED(Tickets),
        Tickets[System] = MAX(Tickets[System]) &&
        Tickets[Project] = MAX(Tickets[Project])
    )
)

How this measure works:

• COUNTROWS(...): This counts the rows in the table that the FILTER function creates.
• ALLSELECTED(Tickets): This is the magic ingredient! It temporarily removes all filters from the Tickets table except for those applied by slicers or other visuals on the report page. This means it will respect your date slicer's selection and the specific System and Project chosen by the matrix's rows/columns.
• Tickets[System] = MAX(Tickets[System]) && Tickets[Project] = MAX(Tickets[Project]): Within each cell of your matrix, MAX(Tickets[System]) and MAX(Tickets[Project]) will return the specific System and Project values for that particular cell. This ensures the count is accurate for that unique combination.

4. Build Your Report Visual

1. Matrix Visual: Add a Matrix visual to your report page.
2. Rows & Columns:
   • Drag Ticket_Every_Combination[Project] to the "Rows" field of your matrix.
   • Drag Ticket_Every_Combination[System] to the "Columns" field of your matrix.
3. Values: Drag your new Count of Tickets by Combination measure to the "Values" field of your matrix.
4. Date Slicer: Add a Slicer visual to your report page. Drag DateTable[Date] into the slicer's field. You can then adjust the slicer to be a "Between" date range for easy selection.

Hi RichOB 

1. Create a date table if you don't have one

Calendar = 
CALENDAR(
    MIN(Tickets[Created_Date]),
    MAX(Tickets[Created_Date])
)

2. Create your combination table with date context

Ticket_Combinations_Filterable =
VAR Systems = VALUES(Tickets[System])
VAR Projects = VALUES(Tickets[Project])
RETURN
ADDCOLUMNS(
    CROSSJOIN(Systems, Projects),
    "Count", 
    VAR CurrentSystem = [System]
    VAR CurrentProject = [Project]
    RETURN
    CALCULATE(
        COUNTROWS(Tickets),
        USERELATIONSHIP('Calendar'[Date], Tickets[Created_Date]),
        Tickets[System] = CurrentSystem,
        Tickets[Project] = CurrentProject
    )
)

How to Use in Your Report

1. Add a date slicer using your Calendar table

2. Create a matrix visual with:

   • Rows: [System] and [Project] from your combination table

   • Values: [Count]

3. The count will automatically filter based on the date selection

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

Hi RichOB,

To make your measure responsive to a date slicer, you need to ensure that the count logic respects the filter context from the Created_Date column. Right now, you're generating all combinations of System and Project using CROSSJOIN, which creates a disconnected virtual table, so the date filter is ignored. The fix is to calculate the count using CALCULATE, which preserves slicer context, including any applied date filters.

Here's how you can revise your DAX:

Ticket_Every_Combination :=
VAR Systems = VALUES(Tickets[System])
VAR Projects = VALUES(Tickets[Project])
RETURN
ADDCOLUMNS(
    CROSSJOIN(Systems, Projects),
    "Count",
    CALCULATE(
        COUNTROWS(Tickets),
        FILTER(
            Tickets,
            Tickets[System] = EARLIER(Tickets[System]) &&
            Tickets[Project] = EARLIER(Tickets[Project])
        )
    )
)

This version keeps your original logic but wraps the COUNTROWS in a CALCULATE, which allows the filter from a slicer on Tickets[Created_Date] to flow through properly. As long as the Created_Date field is in the Tickets table or related to it, your matrix will now update correctly based on the selected date range.

Best regards,