Adding a date column to a combination table

[RichOB]

Hi, I have a table that's based on the DAX below. It's a count of every possible combination based on a project and system when an IT ticket is made.

I need to add a date slicer to the page so that the figures in a matrix table changes with the date. I feel like I either need to add the Created_Date to the existing DAX, or add the Ticket_ID and make a relationship to the source data. How can I add the date or ticket ID to the DAX below, please?

Ticket_Every_Combination =

VAR Systems = VALUES(Tickets[System])

Var Project = VALUES(Tickets[Project])

Return

ADDCOLUMNS(

    CROSSJOIN(Systems,Project),

    "Count", COUNTROWS((FILTER(Tickets, Tickets[System] = EARLIER([System])&& Tickets[Project] = EARLIER(Tickets[Project])))

    ))

Thanks

[freginier · Accepted solution]

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:

Extrait de code

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.

[Elena_Kalina · Accepted solution]

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.

[v-pagayam-msft]

Hi @RichOB ,
Could you please confirm if the issue has been resolved on your end? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.If our answer resolved your query,consider accepting it as solution.
Thank you for your understanding and assistance.

[v-pagayam-msft]

Hi @RichOB ,

Just checking in — did the solution provided help resolve your issue? If yes, please consider marking it as the accepted answer so it can help others facing a similar challenge.If still needed further assistance, feel free to reachout!

Thank you.

[v-pagayam-msft]

Hi @RichOB ,
Thank you @DataNinja777  and @Elena_Kalina , @freginier for the prompt responses!

May I check if your issue has been resolved? If it has, please consider marking the helpful reply or sharing your solution and accepting it. This helps other community members with similar questions find answers more quickly.
Thank you for being a part of Microsfot Fabric Community Forum!

Regards,
Pallavi G.

[DataNinja777 · Accepted solution]

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,

[Elena_Kalina · Accepted solution]

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.

[freginier · Accepted solution]

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:

Extrait de code

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.