Identify the first time work is allocated out

[pkav2000]

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 · Accepted solution]

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]

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