Help with data Modeling for a table with multiple dates

[atjt217]

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

Order details table: 

OrderNumber	CreatedDate	UserId	AssignedDate	AssignedBy	TextedDate	TextsSentBy	TextSuccess
OR-210103001	1/3/2021	A0040	1/3/2021	B3722	1/3/2021	B3722	TRUE
OR-210103001	1/3/2021	A0040	1/3/2021	A0040	1/3/2021	A0040	FALSE
OR-210104105	1/4/2021	B3722	1/4/2021	B3722	1/4/2021	C6347	FALSE
OR-210104001	1/4/2021	C6347	1/4/2021	C6347	1/4/2021	B3722	TRUE
OR-210105001	1/5/2021	A0040	1/5/2021	A0040	1/5/2021	A0040	FALSE
OR-210106001	1/6/2021	A0040	1/6/2021	A0040	1/6/2021	A0040	TRUE
OR-210106002	1/6/2021	B3722	1/6/2021	A0040	1/6/2021	B3722	TRUE
OR-210105113	1/5/2021	A0040	1/5/2021	C6347	1/5/2021	A0040	TRUE
OR-210105116	1/5/2021	B3722	1/5/2021	A0040	1/5/2021	C6347	TRUE
OR-210107044	1/7/2021	C6347	1/7/2021	A0040	1/7/2021	A0040	FALSE
OR-210105062	1/5/2021	C6347	1/5/2021	C6347	1/5/2021	B3722	FALSE
OR-210106906	1/6/2021	C6347	1/6/2021	B3722	1/6/2021	A0040	TRUE
OR-210107100	1/7/2021	B3722	1/7/2021	C6347	1/7/2021	C6347	TRUE
OR-210107020	1/7/2021	C6347	1/7/2021	B3722	1/7/2021	B3722	FALSE
OR-210106068	1/6/2021	C6347	1/6/2021	A0040	1/6/2021	C6347	TRUE
OR-210105169	1/5/2021	B3722	1/6/2021	A0040	1/5/2021	A0040	TRUE

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

UserID	AgentName
A0040	Agent A
B3722	Agent B
C6347	Agent 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: 

AgentName	CreatedDate	AssignedDate	TextedDate	TextSuccess
Agent A	2	1	1	0
Agent B	0	1	1	1
Agent C	0	0	0	0

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

[v-janeyg-msft · Accepted solution]

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]

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

[Ashish_Mathur]

Hi,

Please explain how you arrived at the results in last table.

[atjt217]

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.