DAX Dynamic virtual table Grouping and keep Max date and Max Number

Harsh · January 23, 2023, 2:17pm

Thank You for posting your query onto the Forum.

In order to achieve the results based on the scenario that you’ve mentioned/provided in the Excel file as an expected output. Firstly, you’ll be required to mold the flat file into a proper data model. Below is the screenshot of the data model provided for the reference -

Once data model is created, below are the DAX measures alongwith the screenshot of the final results provided -

1. Total Status Order = 
CALCULATE( MIN( Data[Status Order] ) , 
    SUMMARIZE( 
        Data , 
        'Primary Keys'[DuplicatedPrimaryKey] , 
        Dates[Date] , 
        'Status Types'[Status] ) )

2. Max Status Order = 
IF( NOT ISBLANK( [1. Total Status Order] ) , 
    CALCULATE( MAX( Data[Status Order] ) , 
        ALL( Data ) ,  
        VALUES( 'Primary Keys'[DuplicatedPrimaryKey] ) , 
        VALUES( Dates[Date] ) ) )

3. Total Occurrence = 
VAR _Max_Status_Order = 
[2. Max Status Order]

VAR _Results = 
CALCULATE( SUM( Data[Total occurrence] ) , 
    FILTER( Data , 
        [1. Total Status Order] = _Max_Status_Order ) )

RETURN
_Results

4. Total Occurrence - Totals = 
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            Data , 
            'Primary Keys'[DuplicatedPrimaryKey] , 
            Data[Date] , 
            'Status Types'[Status] ) , 
        "@Totals" , 
        [3. Total Occurrence] ) , 
    [@Totals] )

So now by following the same logic, I tested the results on the data that you had provided in the first tab of the Excel file.

The ID that I’ve selected for testing is - “4-2-002610697420-3761611-05512-228679659028480”.

In the case of date where it’s a tie i.e., on “20th Feb 2017”, Max Status Order is taken into account where “Status Order = 2”. Below is the screenshot provided -

Whereas in the case of next date i.e., on or after “27th Feb 2017”, Max Date and Next Max Status Order is taken into account where “Status Order = 3”. Below is the screenshot provided -

The same is cross-verified with the results in the Excel file. Below is the screenshot provided -

I’m also attaching the working of both the PBIX files for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Important Notes:

1). The solution is provided based on the scenario presented/mentioned in the post and in the Excel file.

2). Please go through the DAX Mastering and other courses which are available onto our education portal in order to learn more and base the understanding of the functions used inside these measures.

3). Below is the link provided for the reference pertaining to the topic - “Fix Incorrect Totals”.

Thanks and Warm Regards,
Harsh

Historical Status Data - Harsh.xlsx (7.6 MB)

Most Recent Dates (Exc. Duplicates) - Harsh - Test.pbix (77.1 KB)

Most Recent Dates (Exc. Duplicates) - Harsh - Actuals.pbix (2.5 MB)