DAX Query - First Instance Of

[kelly_darren]

Hi

I have just completed a new Tabular model and I'm looking to create a measure in dax, but unfortunately I'm failing :-(.  Perhaps someone can assist.

The model is based on SQL Server 2016 and Tabular Services 2016.
There is a single fact table with multiple dimensions and the object being modeled is meetings.

The grain of the fact table is meeting which is based on timeslots during the day and which customers, customer staff and staff can attend.

My model is as following

Fact Meeting - each unique meeting
Date - Meeting - each unique Date of Meeting
Time - Meeting - each unique Meeting Time Slot
Customer - each unique Client (say Shop A, Shop B, Shop C etc etc)
Customer Staff - each unique Client Contact (Shop A - Mary, Shop A - Bob, Shop B - May, Shop B - Paul etc etc)
Staff - each unique Staff

As many Customer Staff, Staff and Meeting Time relate to an individual meeting I use bridge tables for these.

Fact Meeting ---->Meeting Details
Fact Meeting ----> Date - Meeting
Fact Meeting ----> Customer Details
Fact Meeting ----> Bridge Customer Staff ----> Customer Staff
Fact Meeting ----> Bridge Staff ----> Staff
Fact Meeting ----> Bridge Meeting Time ----->. Time Meeting

This is my relationship Diagram

The basic measure is the No. of Meetings which is achieved by Countrows(Fact Meeting)

During any Event, a Customer can attend many meetings and a Customer Staff can also attend many meetings.  What I wish to do is capture the first meeting the Customer attends and the first meeting the Customer Staff attends during the Event.  An event is just an attribute of Meeting Details.

This is the pattern that I used to capture New Client meeting DAX Patterns New and Returning Customers

 ie the first time a Customer has a meeting based on the selected Event, then this would be deemed a New Meeting ie the first one.  This works perfectly, see below

First Meeting - Customer:=
COUNTROWS (
FILTER (
ADDCOLUMNS (
VALUES ( 'Fact Meeting'[Customer Details Key] ),
"PreviousMeeting",
CALCULATE (
COUNTROWS ( 'Fact Meeting' ),
FILTER (
ALL ( 'Date - Meeting' ),
'Date - Meeting'[Meeting Date] < MIN ( 'Date - Meeting'[Meeting Date] )
)
)
),
ISBLANK ( [PreviousMeeting] )
)
)

Now I wish to do the same for a Customers Staff, as they may have multiple meetings over time different time slots over different days, but here I fail.  What the below provides me with is the first meeting Slot per Day, but I need it at the meeting Event Level.

First Meeting - ContactV2:=
COUNTROWS (
FILTER (
ADDCOLUMNS (
VALUES ('Bridge Meeting Details Attendee Client Details'[Meeting Attendee Details Client Key] ),
"PreviousMeeting",
CALCULATE (
COUNTROWS ('Bridge Meeting Details Attendee Client Details' ),
FILTER (
ALL ( 'Date - Meeting' ),
'Date - Meeting'[Meeting Date] < MIN ( 'Date - Meeting'[Meeting Date] )
   ),
FILTER (
ALL ( 'Time - Meeting' ),
'Time - Meeting'[Meeting Time Key] < MIN ( 'Time - Meeting'[Meeting Time Key] )
)
)
),
ISBLANK ( [PreviousMeeting] )
)
)

In tSQL using ROW_Number this would be the result, where the Yellow indicates the expected result based by Customer and the blue underscores the expected result by Customer Staff.

Below are screen grabs showing the results of the first Customer Meeting and the first Customer Staff Meeting

Customer Meeting All


Customer Meeting 1st per Event


Customer Staff Meeting All

Customer Staff Meeting First - the one that isn't working.  Its taking the first meeting per day whilst I;m looking for the first meeting of the event

ie Event ID 7 Would be Meeting Date 2 November @ 13:30

Event ID 8 would be Meeting Date 17th September @ 10:45

I belive this relates to a filter issue but I just don't know.  Can anyone offer some guidance?  

Many thanks.

[v-juanli-msft]

Hi @kelly_darren

After struggling to create the same dataset as yours, I still can't excatly reproduce your scenario.

So I can only give you a general suggestion.

You could consider a workaround by creating rank measures to define the earliest time of the earliest date based on the table which [First Meeting - ContactV2:] =1.

first create a measure to define which date is the earliest for each Event ID, then for the earliest date, calculate which time is the  earliest.

You could use Ranks function.

Reference:

https://blogs.msdn.microsoft.com/danrub/2016/03/19/dynamic-topn-ranking-in-power-bi/

Best Regards

Maggie

[kelly_darren]

@v-juanli-msft Many thanks for replying and providing me with the link to Ranking.  Whilst I have looked at ranking previously, unfortunately I have not gottened it to work (which is down to me, I keep getting 1 😞 )

Would you be able to assit me in using the ranking method?

I have modelled out the screen grabbed code into a power bi report which is accessible from this link Meeting Model

Many thanks.

Darren