I’m trying to dynamically (DAX) group identical items from a column “Duplicate Primary Key” and keep only the MAX “date” and the MAX “StatusNumber” and then sum the number of occurence (needs to be the sum of the occurence column because it is removing duplicates).
Everything is in the attach Excel and PBI File + a test formula with the general idea of the Table function. (I fail to create the intermediary virtual tables so it does only the sum of total occurence)
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 -
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 a lot for this solution it is working 100%!!! I will learn how you proceeded and follow the tutorial you recommended. I was following the MasterDax course already, but the 4 steps formula are sometimes difficult to think of.
