Extracting the date value of the Nth occurence

[danthu]

Hi! I'm new to both PowerBI and DAX so forgive me if something is not properly described!

I have a table of 'Organizations' with one entry for each organization. Then I have a table 'Meetings' with one entry for each meeting that an organisation created. Those tables are related by an OrganizationID. I would like to know the date of the 5th meeting for each individual organization.

My plan is to create a calculated column in 'Organizations' that returns the corresponding date of the 5th meeting for each row. If a particular organization has created less than 5 meetings it should return blank or something similar for that row. I have started creating a DAX expression for the calculated column but it does not work at the moment.

date5thMeeting = IF(COUNTROWS(RELATEDTABLE(Meetings)) > 4; MAX(TOPN(5; RELATEDTABLE(Meetings); Meetings[date]; DESC)); BLANK())

For each row in 'Organizations' it checks whether the organisation has at least 5 entrys in "Meetings", if so I want to return the corresponding date of the 5th meeting. Now here comes the problem. I try to use TOPN to return only the 5 earliest meetings, then I want to find the maximum date of these 5 which should correspond to the date of the 5th meeting. However I cannot use MAX on the table returned by TOPN as MAX expects a single column.

So I guess my question is, how can I extract only the date column out of the table that is returned by TOPN? Any other suggestion on how to acheive the desired result are also very welcome. There could certainly be better way to do this and I'm not sure if my approach will work at all. 

Thank You!

[danthu]

Maybe my way to do this is completely wrong so I will try to explain just my situation so that you can suggest any method to solve it 🙂

I have a table of 'Organizations' with one entry for each organization. Then I have a related table 'Meetings' with one entry for each meeting that an organisation created. Counting from the earliset meeting I would like to know the corresponding date of the 5th meeting that each organisation has created.

Do you have any suggestions on how to accomplish this in PowerBI?

Thank you!

[danthu]

I am still stuck with this 😞 I want to believe that my initial solution would work if I just could find a way to extract a single column out of the table returned by TOPN. So what I want to do is extract a single column out of a table returned by a  DAX function and then use it as input another DAX function.

Specifically I want to extract just the date column out of the returned table that I get from

TOPN(5; RELATEDTABLE(Meetings); Meetings[date]; DESC)

 So that I can run MAX on it to find the maximum date among the 5 earliest dates returned by TOPN. I.e. something like this:

MAX(TOPN(5; RELATEDTABLE(Meetings); Meetings[date]; DESC))

 I would be very happy if someone could help me with this or point me in the right direction!

Thanks!

[Anonymous]

Hi @danthu,

Can you please share some sample data to test?

In addition, if you want to get the max date from specify table, you can try to use below formula:

MaxDate= Calculate(MAX(table[Date]);TOPN(5; Filter(All(table);Table[Date]=Related(Meeting[Date])), table[date]; DESC))

Regards,

Xiaoxin Sheng

[danthu]

@Anonymous

Hi!

Thanks for your response! It cleared things up, I found a solution now!

Much appreciated!

[Anonymous]

Hi @danthu,

Can you please share your solution here? It will be help if others had the similar requirements.

Regards,

Xiaoxin Sheng