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:  OrderNumber CreatedDate UserId AssignedDate AssignedBy Texte...
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.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

Hi,

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

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.