Forum Discussion

ryan_b_fiting's avatar
ryan_b_fiting
Icon for Post Patron rankPost Patron
3 years ago

Measures based on history table

Hello Community -  I am in need of some quick help.  I have been trying to figure this issue out for a while, but cannot seem to get the results I need.   I have an appointment_history table that ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
danextian's avatar
danextian
Icon for Super User rankSuper User
3 years ago

hi ryan_b_fiting,

 

After testing several times, it seems like CROSSFILTER results to an unexpected behaviour - Latest Line Date measure compared against the updated datetime in a given row returnedf an incorrect result -  if both had the same value, test should return false but it wasn't the case. The next  approach is to use a disconnected dates table (no relationship to fact table) and use that in the calculations instead.

 

Please refer to the updated pbix file.

  • ryan_b_fiting's avatar
    ryan_b_fiting
    Icon for Post Patron rankPost Patron
    3 years ago

    Thanks danextian I see that it is now working for the counts and latest line date. 

     

    I believe there are still a few gaps in this.  Maybe the result I am looking for cannot be acheived?  I still am in need of being able to use my connected date table to select a range of appointment dates, but then I need my disconnected date table to on or before the minimum date selected from my connected date table.  Example is if I am looking at all appointments scheduled for 2/1/23 - 2/8/23, I want to count ALL of the unique appointmentIDs that were NOT cancelled before 2/1/23.  If they were cancelled, I want to see the appointment there still, but I want that count to be 0.

     

    Right now, the disconnected date table is has to be manually adjusted and then also, all of the cancelled and Rescheduled appointments do not show up in the table when I include the 'Count' measure in there.

     

    I am trying to figure out how to tweak this, or if I need to create some sort of relationship between the 2 date tables.

     

    You have been extremely helpful so far and I appreciate you.  If you have any tips on the last little bit that needs to be tweaked I would appreciate it!

     

    Thanks
    Ryan

    • danextian's avatar
      danextian
      Icon for Super User rankSuper User
      3 years ago

      The original dates table doesn't need to have no relationship to the fact table. I removed the relationship because I thought of using it instead of a new table but forgot the reinstate the relationship after creating a new dates table.

      • ryan_b_fiting's avatar
        ryan_b_fiting
        Icon for Post Patron rankPost Patron
        3 years ago

        Thanks danextian I think I worded my response pretty poorly so I apologize for that.  I am going to try it again:

         

        I have kept my relationship with my original date table and I am able to look at appointments within the selected date range fine.  My problem comes after that.  I also have a measure that will calculate my future appointments beyond 2/8/23 (also have appointments in the next 7 days, 14 days etc).

        So If I am looking at the 2/1/23-2/8/23 range, I need the 'Count' measure to include anything that was not 'x Canceled' or 'Rescheduled' prior to 2/8/23.  So I need the date tables to interact with each other in a way.

         

        Would this be as simple as creating a One to One bi-directional relationship between the 2 date tables?

         

        Thanks again!