Forum Discussion

kevin_sbi's avatar
kevin_sbi
Frequent Visitor
1 year ago
Solved

Issuing with Calendar Table and two date fields on the same table

Good day, I am running into an issue which I believe is being caused by the fact that I have two date fields on one table and trying to use the date field on a calendar.      We have an incident ta...
Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    The measure is overriding the filters on assignment groups. Try

    Count of Resolved Incidents (Non-SD1) =
CALCULATE (
    COUNTROWS ( 'service_now incident' ),
    USERELATIONSHIP ( 'Calendar'[Date], 'service_now incident'[Resolved Short Date] ),
    'service_now incident'[Resolved Short Date] >= DATE ( 2024, 1, 1 ),
    KEEPFILTERS (
        'service_now incident'[assignment_group] <> "ITOPS - SD - Level 1"
            && CONTAINSSTRING ( 'service_now incident'[assignment_group], "ITOPS" )
    )
)
johnt75's avatar
johnt75
Super User
1 year ago

I think its the use of FILTER on the table as a whole. That's expanding out to the calendar table using the relationship which is active at the time, and that is the relationship with opened date.

Try

Count of Resolved Incidents (Non-SD1) =
CALCULATE (
    COUNTROWS ( 'service_now incident' ),
    USERELATIONSHIP ( 'Calendar'[Date], 'service_now incident'[Resolved Short Date] ),
    'service_now incident'[Resolved Short Date] >= DATE ( 2024, 1, 1 ),
    'service_now incident'[assignment_group] <> "ITOPS - SD - Level 1"
        && CONTAINSSTRING ( 'service_now incident'[assignment_group], "ITOPS" )
)
kevin_sbi's avatar
kevin_sbi
Frequent Visitor
1 year ago

Thank you John!  This actually did resolve the issue in a table by itself and I created a single table and everything looks great!

 

 

 

Now, I am running into an issue when I use the resolved count to create a matrix with month year sort and assignment groups.  The numbers for a row are all the same, and it appears to be ignoring the conditions I had set (The 1- groups should not be showing up).   I tried plugging in just the Resolved Month year sort but same thing.

 

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    What are assignment groups? What relationship do they have with the date table and the fact table ?

    • kevin_sbi's avatar
      kevin_sbi
      Frequent Visitor
      1 year ago

      Assignment group is part of the incident table, so just the two existing relationships (resolved [inactive] and created [active]) to the date table.   

       

      Here is the visual definition:

       

       

      I did the same exact visual for created, with the value being count of incidents and it works fine:

       

      Thanks in advance for any help!

       

      • johnt75's avatar
        johnt75
        Super User
        1 year ago

        The measure is overriding the filters on assignment groups. Try

        Count of Resolved Incidents (Non-SD1) =
CALCULATE (
    COUNTROWS ( 'service_now incident' ),
    USERELATIONSHIP ( 'Calendar'[Date], 'service_now incident'[Resolved Short Date] ),
    'service_now incident'[Resolved Short Date] >= DATE ( 2024, 1, 1 ),
    KEEPFILTERS (
        'service_now incident'[assignment_group] <> "ITOPS - SD - Level 1"
            && CONTAINSSTRING ( 'service_now incident'[assignment_group], "ITOPS" )
    )
)