Forum Discussion
Help?
This DAX calculated table might work as well
Sample file attached as well
From the Modelling tab>>New Table
New Table = VAR Filter_Dependents = CALCULATETABLE ( Grouped, FILTER ( SUMMARIZE ( Grouped, Grouped[Name], [Dependent], "Count", DISTINCTCOUNT ( Grouped[Form No.] ) ), [Count] > 1 ) ) VAR RANK_Forms = FILTER ( ADDCOLUMNS ( Filter_Dependents, "RANK", RANKX ( FILTER ( Filter_Dependents, [Name] = EARLIER ( [Name] ) ), [Form No.], , ASC, DENSE ) ), [RANK] > 1 ) RETURN SUMMARIZE ( RANK_Forms, [Name], [Dependent], "Paid", SUM ( Grouped[Paid] ) )
This requires a few steps
1. Order by Form No. Ascending
2. Serialize the unique combination of Name and Dependent.
3. Filter out the first occurence on the index
4. Then do a group by on name and dependent while summing the paid column.
Example file has all the steps:
https://drive.google.com/file/d/152XDCLRmkex2xLqweagkwY-EXzHBi6E2/view?usp=sharing
This video shows how to do the serialization:
https://www.youtube.com/watch?time_continue=36&v=-3KFZaYImEY
Let me know if that helps.
This DAX calculated table might work as well
Sample file attached as well
From the Modelling tab>>New Table
New Table = VAR Filter_Dependents = CALCULATETABLE ( Grouped, FILTER ( SUMMARIZE ( Grouped, Grouped[Name], [Dependent], "Count", DISTINCTCOUNT ( Grouped[Form No.] ) ), [Count] > 1 ) ) VAR RANK_Forms = FILTER ( ADDCOLUMNS ( Filter_Dependents, "RANK", RANKX ( FILTER ( Filter_Dependents, [Name] = EARLIER ( [Name] ) ), [Form No.], , ASC, DENSE ) ), [RANK] > 1 ) RETURN SUMMARIZE ( RANK_Forms, [Name], [Dependent], "Paid", SUM ( Grouped[Paid] ) )I tried this and got the same results. Nice Solution.
This is a very elegant solution! Thank you for your help! :)
