Forum Discussion

dd999's avatar
dd999
Icon for Advocate I rankAdvocate I
10 months ago
Solved

3 different timeSortOrders. currently 3 different tables. a better design for Table TimeSortOrder

I have a table, Time12hrOrder

with columns

24Hour. unique time value

12hour. convert 24hour to 12hour

TimeSortOrder. set the order for Time to display on visuals 

Unique12hr_Adjusted is the clean time to be used on Visuals. to make user friendly

 

24Hour is  SortByColumn TimeSortOrder.

 

The orginial company business hours are 9am - 5pm. The requirement is show total calls by hour during business hours 9am - 5pm

 

A new company has merged with our company.  Their business hours are 8.30am - 4.30pm. After hours are 4.30pm - 8.30am. and require to show the Day total calls between 8.30am - 4.30pm and After hours/evening total phone calls 4.30pm to 8.30am

 

in the orginial table Time12hrOrder I added a new column 'Hour24Order_Comp2_Day' to add sortOrder however could not get it work. Therefore ended up creating 3 tables, with exact same values the only difference is 1 column TimeSortOrder for

Company 1 - TimeSortOrder 9am - 5pm. Start with 9am

Company 2 Day - TimeSortOrder 8.30am - 4.30pm.  Start with 8.30am

Company 2 Evening TimeSortOrder  4.30pm - 8.30am. Start with 4.30pm

 

is there a better design to accomodate for this, instead of 3 seperate tables

 

 

eg visuals using the 3 different Tables sortOrder

 

TIA

 

Need Help

4 Replies

    • v-menakakota's avatar
      v-menakakota
      Icon for Community Support rankCommunity Support
      10 months ago

      Hi dd999 

      Thanks for reaching out to the Microsoft fabric community forum.

       

      I would also take a moment to thank danextian    , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

      I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

       

      Best Regards, 
      Community Support Team

      • v-menakakota's avatar
        v-menakakota
        Icon for Community Support rankCommunity Support
        9 months ago

        Hi dd999 ,

        I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

         

        Best Regards, 
        Community Support Team

  • Mauro89's avatar
    Mauro89
    Icon for Super User rankSuper User
    10 months ago

    Hi dd999,

     

    you can replace all three Time tables with one master Time table + multiple sort order columns, and then choose which sort order to apply per visual.

    Recommended Design — One Time Table

    Keep your main Time dimension:

     

    Time (DimTime)
    - TimeID (unique key, ex. 24-hour decimal: 8.5, 9.0 …)
    - Time24 (08:30, 09:00 …)
    - Time12 (8:30 AM, 9 AM …)
    - IsBusiness_C1 (TRUE 9–17)
    - IsBusiness_C2_Day (TRUE 8:30–16:30)
    - IsBusiness_C2_Evening (TRUE 16:30–08:30)
    - Sort_C1 - Sort_C2_Day - Sort_C2_Evening
     

    You already created similar fields — the key is you must set SortByColumn only on demand.

    How to use in visuals

    You do not set a single permanent SortByColumn on the column used in visuals.

    Instead:

    1. Duplicate the display column
      Example:

       
      DisplayTime_C1 = DimTime[Time12] DisplayTime_C2_Day = DimTime[Time12] DisplayTime_C2_Evening = DimTime[Time12]

    2. For each display column, set the appropriate SortByColumn:

       
      DisplayTime_C1 → SortByColumn = Sort_C1 DisplayTime_C2_Day → SortByColumn = Sort_C2_Day DisplayTime_C2_Evening → SortByColumn = Sort_C2_Evening

    3. In visuals, pick the correct display column based on the scenario.

     

    Best regards!

    PS: If you find this helpful, consider leaving some kudos and mark it as solution.