Forum Discussion

atjt217's avatar
atjt217
Helper III
5 years ago
Solved

Help with data Modeling for a table with multiple dates

Hello PBI wizards, Please help me out. I have 2 tables that contains the Order details and the User ID:

 

Order details table: 

OrderNumberCreatedDateUserIdAssignedDateAssignedByTextedDateTextsSentByTextSuccess
OR-2101030011/3/2021A00401/3/2021B37221/3/2021B3722TRUE
OR-2101030011/3/2021A00401/3/2021A00401/3/2021A0040FALSE
OR-2101041051/4/2021B37221/4/2021B37221/4/2021C6347FALSE
OR-2101040011/4/2021C63471/4/2021C63471/4/2021B3722TRUE
OR-2101050011/5/2021A00401/5/2021A00401/5/2021A0040FALSE
OR-2101060011/6/2021A00401/6/2021A00401/6/2021A0040TRUE
OR-2101060021/6/2021B37221/6/2021A00401/6/2021B3722TRUE
OR-2101051131/5/2021A00401/5/2021C63471/5/2021A0040TRUE
OR-2101051161/5/2021B37221/5/2021A00401/5/2021C6347TRUE
OR-2101070441/7/2021C63471/7/2021A00401/7/2021A0040FALSE
OR-2101050621/5/2021C63471/5/2021C63471/5/2021B3722FALSE
OR-2101069061/6/2021C63471/6/2021B37221/6/2021A0040TRUE
OR-2101071001/7/2021B37221/7/2021C63471/7/2021C6347TRUE
OR-2101070201/7/2021C63471/7/2021B37221/7/2021B3722FALSE
OR-2101060681/6/2021C63471/6/2021A00401/6/2021C6347TRUE
OR-2101051691/5/2021B37221/6/2021A00401/5/2021A0040TRUE

 

User Id: (Note: on the order details table the User Id is also used on Assignedby and Texted by)

UserIDAgentName
A0040Agent A
B3722Agent B
C6347Agent C

 

What im trying to do is figure out the number the following: 

  • The count of Order created each agent made based of the created date 
  • The count of Order that was assigned based of Assigned date 
  • The count of Order each agent sent a text to based of TextedDate and how many from those were successful 

For example: 

On 1/13/2021 for Created Date, Asigned Date and Texted Date: 

AgentNameCreatedDate AssignedDateTextedDateTextSuccess
Agent A2110
Agent B0111
Agent C0000

 

How do i create a relationship for these 2 tables and what formula should i use to get a count? 

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi,  atjt217 

     

    The relationship is more complicated. If you just want this result, it is not recommended to establish a relationship.

    You can create a date table that you want to query the time period, and create four measures to calculate your desired result.

    Like this:

    Table = CALENDAR(DATE(2021,1,1),DATE(2021,1,10))
    Measure1 =
CALCULATE (
    COUNTROWS ( 'Order' ),
    FILTER (
        ALL ( 'Order' ),
        [CreatedDate] = SELECTEDVALUE ( 'Table'[Date] )
            && [UserId] = SELECTEDVALUE ( User[UserID] )
    )
) + 0

    Here is my sample .pbix file.Hope it helps.

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    Hi,  atjt217 

     

    The relationship is more complicated. If you just want this result, it is not recommended to establish a relationship.

    You can create a date table that you want to query the time period, and create four measures to calculate your desired result.

    Like this:

    Table = CALENDAR(DATE(2021,1,1),DATE(2021,1,10))
    Measure1 =
CALCULATE (
    COUNTROWS ( 'Order' ),
    FILTER (
        ALL ( 'Order' ),
        [CreatedDate] = SELECTEDVALUE ( 'Table'[Date] )
            && [UserId] = SELECTEDVALUE ( User[UserID] )
    )
) + 0

    Here is my sample .pbix file.Hope it helps.

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • atjt217's avatar
      atjt217
      Helper III
      5 years ago

      Thank you! i think this is the best solution for it. I appreciate your help! 

    • atjt217's avatar
      atjt217
      Helper III
      5 years ago

      Hi Ashish, 

      That is what im trying to achieve i don't have a solution for it i manually counted the last table on excel. I want to know what is possible with Power BI or anything close to that even if they are not on the same table. As long as i can get a count of what each Agent created, assigned and texted.