Forum Discussion
Different Dates based upon 2 different columns
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 |
10 Replies
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 __MaxDateGreg_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.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
Greg_Deckler sorry forgot to mention stage name is in a different table to latest evcent flag however they linked via a relationship
antlufc What is the relationship? As in, what forms the relationship?
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
