Forum Discussion

PPStar's avatar
PPStar
Helper V
2 years ago
Solved

How to get all data on chart

Hello.  I have 2 tables. A Tickets Table and a Data Table.    Date Table is related to the Tickets Table via a 1:M relationship with Date (From Date Table) and opened_at (From All Tickets Table) ...
Need Help
  • PPStar's avatar
    PPStar
    2 years ago

    I have been playing around, and i thnk my measures were wrong. 

    I fixed the measures and it appears to be working ok now. 

     

     

Slim-'s avatar
Slim-
Frequent Visitor
2 years ago

Hi,

 

Problem is coming from relation : the relation is between the opening date and the calendar table date.

Therefore when you use the calendar table date on x axis, the data is shown for the calendar table date -> opening date.

 

To counter this, keep your relationship between opened_at and your calendar table but also create another relationship between closed_at and the date from your calendar table. This second relationship won't be active since you can only have one relationship active : you can now create a measure using the function "CALCULATE" with "USERELATIONSHIP" that will override the relationship active to use the one you specify.

 

Regards

  • PPStar's avatar
    PPStar
    Helper V
    2 years ago

    Thank you very much. I will try this shortly. 

  • PPStar's avatar
    PPStar
    Helper V
    2 years ago

    Im getting a odd result. 

    SO i have the followng relationship : 

     

     

    Date 1:M to closed_at is Inactve. 

    Date 1:M to opened_at is active

     

    I created two measures : 

    Closed_At Count = CALCULATE(COUNTA('All Tickets'[closed_at]), USERELATIONSHIP('Calendar'[Date],'All Tickets'[closed_at]))
     
    Opened_at = CALCULATE(COUNTROWS('All Tickets'))
     
    When i plot these visuals on my graph, 
     

     

    Howver, if i clikc on Closed for March, it shows me Closed_at for April in the table below. (March 2024 is highligted) 

     

    Are my measures wrong?

     

    All i want is count of tickets opened and closed per month. 

     

    Thanks

    • Slim-'s avatar
      Slim-
      Frequent Visitor
      2 years ago

      PPStar,

      If you look at your graph in the original post, values were 46 opened and 43 closed in march.

      After using USERLATIONSHIP, you have 46 opened and 42 closed.

      I think your measures are good.

      However, the columns in the table below do not seem to correspond to the graph (total 46 and 43 in table vs 46 and 42 in graph).

       

      Try to plot in your table two columns : 'Calendar'[Date] and [Closed_At Count]. You should have the right numbers.

       

      Let me know if not,

      Regards

      • PPStar's avatar
        PPStar
        Helper V
        2 years ago

        I have been playing around, and i thnk my measures were wrong. 

        I fixed the measures and it appears to be working ok now.