Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count rows based on a filter/date

Hello,
I'm getting lost a bit here. For a specific visual I need to calculate Service Request created in a specific timeframe as well as service request escalated to the design team. This will be used to display on one visual (so I have the unrelated date table) since the time when the service requests get created and escalated to the design team will be different (day, month, year). Also, I'm planning to calculate a ratio to display  tickets to design escalation metrics 


Sample table looks like follows:

Service request table
Service Request ID; occurred; escalated to design;


where the occurred and escalated to design contains the date

 

I also have an unrelated calendar table
Date
Date;WeekNumber


I'm about to use DAX but seems that I'm getting it all wrong


Count Service Requests occurred  = CALCULATE(COUNTA('Service request table'[Service Request ID]]), FILTER(ALLSELECTED('Service request table'), 'Service request table'[Occurred]=SELECTEDVALUE('Date'[Date])))

 

and 

 

Count Service Requests escalated to design = CALCULATE(COUNTA('Service request table'[Service Request ID]]), FILTER(ALLSELECTED('Service request table'), 'Service request table'[escalated to design]=SELECTEDVALUE('Date'[Date])))

 

I have then a visual with the axis DATE (from the unrelated table) but does not display anything.  The same wit ha card when I apply filter.  I'm struggling to understand DAX and the DATE filter logic. 

 

Appreciate if you can point me to the appropriate direction with this.

 

 

Cheers,

 

Jiri

 

 

Need Help
Tips and Tricks
  • BA_Pete's avatar
    BA_Pete
    5 years ago

    Hi Anonymous ,

     

    First, you need to relate your calendar table to your fact table.

    Drag [Date] from calendar to [occurred] - this should apply an ACTIVE relationship.

    Then drag [Date] from calendar to [escalated to design] - this should apply an INACTIVE relationship.

    Both relationships should show as calendar is the ONE side and Service Request Table is the MANY side.

     

    Then set up these measures:

    _requestsOccurred = DISTINCTCOUNT('Service request table'[Service Request ID])

_requestsEscalated =
CALCULATE(
  DISTINCTCOUNT('Service request table'[Service Request ID]),
  USERELATIONSHIP('Service request table'[escalated to design], Calendar[Date])
)

     

    Use the Calendar table date/week number as your visual axis.

    You should now be able to use both of these measures in the same visual and view their values within a common timeframe.

     

    Pete

    Pete

4 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    5 years ago

    Hi Anonymous ,

     

    First, you need to relate your calendar table to your fact table.

    Drag [Date] from calendar to [occurred] - this should apply an ACTIVE relationship.

    Then drag [Date] from calendar to [escalated to design] - this should apply an INACTIVE relationship.

    Both relationships should show as calendar is the ONE side and Service Request Table is the MANY side.

     

    Then set up these measures:

    _requestsOccurred = DISTINCTCOUNT('Service request table'[Service Request ID])

_requestsEscalated =
CALCULATE(
  DISTINCTCOUNT('Service request table'[Service Request ID]),
  USERELATIONSHIP('Service request table'[escalated to design], Calendar[Date])
)

     

    Use the Calendar table date/week number as your visual axis.

    You should now be able to use both of these measures in the same visual and view their values within a common timeframe.

     

    Pete

    Pete

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you for your response, Pete,

      I was trying to avoid using the relationship because whenever I do it, it "breaks" the Data Hierarchy on the [Occurred]. I tied Active as well as Inactive buy always the same. This apparently breaks existing reports and visuals. Is there any way to avoid this? Shall I create [Copy of Occurred] to do so? 

       

      Regardless, I was playing with this and still not getting the expected result. Will try again but will appreciate any guidance on this.

       

      Edit after a coup of tea: 

      My date table has the date in format  is 1.1.2020 0:00:00

       

      While my occurred is always in 1.1.2020 10:30:15 or whatever was the exact time. I'm going to add columns and strip off the time

       

      Jiri

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Pete,

        all working now. I had to revert the fact table data fields to the start of the day to match my calendar. 

         

        Thanks so much for your pointing me to the direction. I was not able to understand how to use relationship in DAX and your simple example helped me to understand it.  I'm already planning to use this for a backlog calculation (created vs closed scoring).

         

        Cheers,

         

        Jiri