Measures count not segregating accordingly in visualization

[enricm1982]

Hello,

I'm currently stuck with some results I need.  These are the details:

-'Tickets' Data Source/Table
-Date Table:  Which contains dates and some other formulas I need to pull out the information.
These two have two relationships.  1 Active: Tickets to Date Table, Many to One, Cross Filter Single: Resolved Date to Date.  The 2nd relationship its an inactive one; Tickets to Date Table, Many to One, Cross Filter Single, Created Date to Date.

-Im currently using Line and Clustered Column Chart
-Total Count of Resolved CR Tickets (Measure)
-Total Count of Created Issue Tickets (Measure)
In addition two tables visuals:
Resolved CR Tickets Details, sorted by Resolved Date.

Created Issue Tickets, sorted by Created date.

In the X-Axis of the Graph I have Year and Month.  
Y Axis: Resolved CR Tickets Details & Created Issue Tickets.
The Problem, lets say I click on April 2025:
The Count of the Issue Tickets is accurate and sorted under the month it was created and matches with the info in Created Issue Tickets table.
However, for the count of created CR tickets it gives me results that are not from April (or any other month I try) in the table visual of Resolved CR Ticket Details.  What I'm noticing, even using USERELATIONSHIP, the Resolved CR Tickets are being sorted as Created Date.  I even used at somepoint TREATAS Resolved Date to force the system to actually sort them in the correct months.

I also tried creating a secondary Date Table and a M query referencing the Tickets table "as a copy".
I'm really stuck here, not sure how to achieve this.  

Ideas? 

[v-veshwara-msft]

Hi @enricm1982 ,
Following up to see if your query has been resolved. If any of the responses helped, please consider marking the relevant reply as the 'Accepted Solution' to assist others with similar questions.

If you further need assistance, feel free to reach out.

Thank you.

[v-veshwara-msft]

Hi @enricm1982, 

May I ask if your query has been resolved and if the solution provided has addressed your needs?. 

If you need any further assistance, feel free to reach out.

Thank you.

[v-veshwara-msft]

Hi @enricm1982 ,

Just checking in to see if you query is resolved and if any responses were helpful. If so, kindly consider marking the helpful reply as 'Accepted Solution' to help others with similar queries. 

Otherwise, feel free to reach out for further assistance.

Thank you.
Thanks @KNP for your continued assistance.

[v-veshwara-msft · Accepted solution]

Hi @enricm1982 ,

Thanks for reaching out to Microsoft Fabric Community.

I tried to reproduce the issue based on the details you shared with some sample data. Here is a summary of my repro setup:

• A single Date table marked as the date table.

• Tickets table with both Created Date and Resolved Date columns.

• Active relationship between Tickets[Resolved Date] and Date[Date].

• Inactive relationship between Tickets[Created Date] and Date[Date].

• Measures using USERELATIONSHIP and TREATAS to switch between these dates.

• Visuals with YearMonth on X-axis showing counts for Created Issues and Resolved CRs.

To better align the filtering with Resolved Date, the measure for Total Resolved CRs was modified using the USERELATIONSHIP function, which produced the correct filtering behavior in the visual:

Total Resolved CRs = 
CALCULATE(
    COUNTROWS('Tickets'),
    'Tickets'[Status] IN {"Resolved", "Closed"},
    NOT(ISBLANK('Tickets'[Resolved Date])),
    'Tickets'[SR - Change Request] = "Yes",
    USERELATIONSHIP('Date'[Date], 'Tickets'[Resolved Date])
)

When I click on a month (e.g April 2024)

If this didn’t fully capture your scenario or meet your expectations, please let us know.

To address the filtering behavior in a single chart, here is an important consideration:

Because Power BI supports only one active relationship between tables at a time, using two date columns related to a single Date table creates filter ambiguity. Measures using USERELATIONSHIP or TREATAS help but don’t fully solve this when visuals filter on both dates.

The best practice is to create two separate Date tables:

• One linked actively to Created Date.

• Another linked actively to Resolved Date.

Then use separate visuals for Created and Resolved counts, syncing slicers between these two Date tables to maintain user-friendly filtering.

This approach ensures correct filtering and aggregation without conflicting filter context.

If possible, please share a sample .pbix file (without any sensitive data) that reproduces the issue. That would help us guide you more precisely and confirm if the dual date table approach would resolve it completely in your context.

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.

Thank you,
Vinay Kumar Eshwara.

Attaching .pbix file for reference.

[KNP]

There are a whole lot of debugging steps I would normally take when troubleshooting this type of issue.

• Check the relationships and column data types are correct.
• Check that the relationship that is supposed to be active, is.
• Check any other measures that may be using TREATAS or USERELATIONSHIP in the visuals or filters on the page.
• Drop a slicer on the page for CreatedDate and test visual interaction
• Drop a slicer on the page for ResolvedDate and test visual interaction

Many other things that would probably come to mind if I were debugging the model.

I sometimes use ChatGPT/Copilot for a sounding board (never to answer questions, like some on here 😂), in case it triggers any ideas for you...

https://chatgpt.com/share/6840a36b-b098-8010-b07c-de99f01d5ef7

[KNP]

I've tried to recreate the issue but have not been able to.

Unfortunately, I don't have enough info to help resolve this.

I assume date table is marked as a date table and that year and month in the chart are coming from the date table.

What happens to the two tables when you select another month?

What happens if you select a single row from the CR table? Does it filter the chart correctly?

I'll keep thinking about it and see if any other ideas come up.

[enricm1982]

Date table marked as Date Table, yes.

Month and Year, from Date Table or the Tickets table, same outcome.
If I select another month, same issue.
If I select a ticket in the CR Table (right now selected one resolved in 10/03/2024 (Created: August 2024) then the chart show me that one (1) ticket in August, proving that indeed is categorizing by created date not resolved.

Thanks,

ECM

[KNP]

Can you share a screenshot of your model?

[enricm1982]

Unfortunately no, sensitive data.

What are you trying to see?  Perhaps I can bring more context.

[KNP]

Can you please paste the code for your Total count measures? And also a screenshot of the chart highlighting incorrect values? 

I'm struggling to visualise this.

[enricm1982]

Created Issues = 
CALCULATE(
    [Count Issues RC],
    USERELATIONSHIP('Date'[Date], 'Tickets'[Created Date])
)

Count Issues RC = 
COUNT('Tickets'[Issues Root Cause for Change Success])

Total Resolved CRs = 
CALCULATE(
    COUNTROWS('Tickets'),
    'Tickets'[Status] IN {"Resolved", "Closed"},
    NOT(ISBLANK('Tickets'[Resolved Date])),
    NOT(ISBLANK('Tickets'[SR - Change Request])),
    TREATAS(VALUES('Date'[Date]), 'Tickets'[Resolved Date])
)

"SR-Change Request" field cannot be shared since it contains sensitive data.  It is basically a calculated column that filter the data I need for the CRs.

 
Screenshot with the problem:

As you can see the count for CRs Resolved is wrong and providing tickets resolved in other months.  I added the created date column as reference in the CRs table, you can see is extracting those by created date and not resolved date.

Thanks in advance,
ECM