topic Re: Different Dates based upon 2 different columns in DAX Commands and Tips

Different Dates based upon 2 different columns

antlufc — Wed, 04 Oct 2023 10:36:05 GMT

The table below shows MQLs by latest date and latest event flag.

I would like to show the volume of MQLs that are Not Accepted or Achived using the MQLs - latest date.

So in my below examples i would like Key 7442051 to be counted and use the date 22/9/2023 and 7085123 to be counted and use 1/9/2023. It may also occur where they are on the same date and some instances where Not Accpted and Archived are either not the latest event or may not appear at all.

I can not add a column or amend the power query so i would like to solve this problem using DAX.

(Column Names as per below, this is only in relation to the table below

Column 1 = Stage Name
Column 2 = MQLs latest Date

Column 3 = latest event flag
Colimn 4 = date

Column 5 = Key)

New	1	0	22/09/2023	7442051
Working	0	0	22/09/2023	7442051
Working	0	0	26/09/2023	7442051
Working	0	0	27/09/2023	7442051
Not Accepted	0	1	27/09/2023	7442051
New	0	0	09/08/2023	7085123
New	1	0	01/09/2023	7085123
Working	0	0	01/09/2023	7085123
Working	0	0	05/09/2023	7085123
Archived	0	1	05/09/2023	7085123

Re: Different Dates based upon 2 different columns

Greg_Deckler — Wed, 04 Oct 2023 11:17:07 GMT

antlufc Not sure I fully understand but perhaps something like:

Measure = VAR __Table = FILTER('Table', [MQLs latest Date] = 1) VAR __MaxDate = MAXX( __Table, [date] ) RETURN __MaxDate

Re: Different Dates based upon 2 different columns

antlufc — Wed, 04 Oct 2023 11:52:55 GMT

Greg_Deckler - i need to take into account if the MQL is Archived / Not Accpeted and take into account the date of the latest MQL.

Would be - provide the count of MQLs latest date where the latest event flag = 1 and the stage name = Not Accpted or Archived.

Re: Different Dates based upon 2 different columns

Greg_Deckler — Wed, 04 Oct 2023 12:21:32 GMT

antlufc OK, how about this? PBIX attached below signature.

Measure = VAR __Statuses = { "Archived", "Not Accepted" } VAR __LatestStatus = MAXX( FILTER( 'Table', [latest event flag] = 1 ), [Stage Name] ) VAR __MaxDate = MAXX( FILTER('Table', [MQLs latest Date] = 1), [date] ) VAR __Result = IF( __LatestStatus IN __Statuses, __MaxDate, BLANK() ) RETURN __Result

Re: Different Dates based upon 2 different columns

antlufc — Wed, 04 Oct 2023 12:27:30 GMT

Greg_Deckler sorry forgot to mention stage name is in a different table to latest evcent flag however they linked via a relationship

Re: Different Dates based upon 2 different columns

Greg_Deckler — Wed, 04 Oct 2023 12:52:01 GMT

antlufc What is the relationship? As in, what forms the relationship?

Re: Different Dates based upon 2 different columns

antlufc — Wed, 04 Oct 2023 12:56:27 GMT

Greg_Deckler - date key based on event date from the fact table to date key in the date dimension. The event date could be associated with events prior and post the MQL latest date

Re: Different Dates based upon 2 different columns

Greg_Deckler — Wed, 04 Oct 2023 13:07:33 GMT

antlufc I'm not quite following this. So you have a Date dimension and that includes what the Stage is for each Key based on which Date? Maybe post a picture of your data model or at least the relevant tables. I'm not understanding how you are arriving at the correct Stage for a specified Key and Date.

Re: Different Dates based upon 2 different columns

antlufc — Wed, 04 Oct 2023 13:24:46 GMT

Greg_Deckler

date dimension - which provides the date linked to fact events via date key

fact events table - which provides the MQL latest date, key and date key which links to date dimension based on the date an event happened and dim events via the key

dim events - which contains the stage name which is linked to fact events via the key.

Re: Different Dates based upon 2 different columns

Greg_Deckler — Wed, 04 Oct 2023 14:12:25 GMT

antlufc So the dim events table also has a date so you know what stage a key was in on a specific date?

Re: Different Dates based upon 2 different columns

antlufc — Thu, 05 Oct 2023 07:31:45 GMT

Greg_Deckler - no dim events only has the stage name.

The date where the specific event has happened is in fact events.