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" )
    )
)
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
Icon for Super User rankSuper 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
      Icon for Super User rankSuper 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" )
    )
)