Forum Discussion

pkav2000's avatar
pkav2000
Regular Visitor
6 years ago
Solved

Identify the first time work is allocated out

Hi folks, 

Can someone help me resolve an issue. I'm trying to identify the first time a piece of work was allocated out.

I've created a sample pbx to show people what I mean, but in summary...

 

I've 3 tables - Ticket (containing ticket details), Allocation (containing the start and end time of the work allocation), Ticket to Allocation (which is one to many - 1 ticket can have many allocations)

 

 

 

 

 

 

 

 

 

So based on the above data, I'd like to be able to report:

TICKET - FIRST ALLOCATION

Ticket 1 - Allocation 1

Ticket 2 - Allocation 2

Ticket 3 - Allocation 3

Ticket 4 - Allocation 2

Ticket 5 - Allocation 2

 

I've tired a few different dax queries but I'm hacking at something I've no knowledge on right now as I'm still trying to learn this stuff!

 

An example pbx is here - https://www.dropbox.com/s/w32rxofsw517mtl/First%20Occ%20Sample.pbix?dl=0

 

Thanks,

 

Paul

  • speedramps's avatar
    speedramps
    6 years ago

    Hi Pakv

    Please consider this solution and leave kudos ....

    First add a new report  with a table visual with

    Ticket[Ticket]

    Earliest  Allocation[Start Date]

     

    Note that this displays all the start dates for each ticket. How annoying ðŸ˜«

     

    Now go to the relationship pane and click on each relationship and set them to Cross Filter Direction = Both and click ok. Check they are both set carefully.

     

    Go back to the visual table you added before.  It now just shows the first start dates for each ticket. ðŸ˜ƒ

     

     "Cross Filter" does what it says on the tin and applies the filters from one table across to another, when using a bridge table. 

2 Replies

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    6 years ago

    Hi Pakv

    Please consider this solution and leave kudos ....

    First add a new report  with a table visual with

    Ticket[Ticket]

    Earliest  Allocation[Start Date]

     

    Note that this displays all the start dates for each ticket. How annoying ðŸ˜«

     

    Now go to the relationship pane and click on each relationship and set them to Cross Filter Direction = Both and click ok. Check they are both set carefully.

     

    Go back to the visual table you added before.  It now just shows the first start dates for each ticket. ðŸ˜ƒ

     

     "Cross Filter" does what it says on the tin and applies the filters from one table across to another, when using a bridge table. 

    • pkav2000's avatar
      pkav2000
      Regular Visitor
      6 years ago

      You are a hero.

       

      I was trying to crack a nut with a hammer using DAX and all sorts of mad queries and joins having totally forgot about that option!

       

      Thank you so much! 

       

      Paul