Trouble trying to replicate GROUP BY/DISTINCT and SUM in PowerPivot with DAX

[hairycoo]

I have the following records table (ignore the commas)


Record, Channel, Path Length, Unq Path Length
8726, direct, 2, 2
8726, direct, 2, NULL
8726, organic, 1, 1
28364, paid, 2, 2
28364, display, 2, NULL
287364, email, 4, 4
287364, email, 4, NULL
287364, direct, 4, NULL
287364, email, 4, NULL


Each record (unqPath) can have multiple channels. For each record I have Path length which is the number of channels associated with that record (this was achieved in SQL doing a count and partitioning it over record name). I have also created another column called UnqPathLength where I list the PathLength for each record only once (on the row associated with the 1st channel)


In PowerPivot, I want to sum the PathLength of all records where a certain channel appears. So direct appears in 2 records which have Path Lengths 2 and 4 respectively so I'd like to have 6 for that.


I have tried everything from using summarise, distinct but the path length is almost always double counted when the same channel appears twice in the same record. Also tried using unqPathLength (figured this would help with the duplicate counting) but it only works when the channel is on the same row and returns incorrect results.


Here are some of my efforts

[FONT=inherit][COLOR=#666600][FONT=inherit]#[/FONT][/COLOR][COLOR=#000000][FONT=inherit] pathLengthChannel[/FONT][/COLOR][COLOR=#666600][FONT=inherit]:=[/FONT][/COLOR][COLOR=#000000][FONT=inherit]  SUMX[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#000000][FONT=inherit]    SUMMARIZE[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#008800][FONT=inherit]'query'[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#008800][FONT=inherit]'query'[/FONT][/COLOR][COLOR=#666600][FONT=inherit][[/FONT][/COLOR][COLOR=#000000][FONT=inherit]unqPath[/FONT][/COLOR][COLOR=#666600][FONT=inherit]],[/FONT][/COLOR][COLOR=#008800][FONT=inherit]'query'[/FONT][/COLOR][COLOR=#666600][FONT=inherit][[/FONT][/COLOR][COLOR=#000000][FONT=inherit]channel[/FONT][/COLOR][COLOR=#666600][FONT=inherit]],[/FONT][/COLOR][COLOR=#008800][FONT=inherit]'query'[/FONT][/COLOR][COLOR=#666600][FONT=inherit][[/FONT][/COLOR][COLOR=#000000][FONT=inherit]pathLength[/FONT][/COLOR][COLOR=#666600][FONT=inherit]],[/FONT][/COLOR][COLOR=#008800][FONT=inherit]"pathLengthChannel0"[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit]    SUM[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#008800][FONT=inherit]'query'[/FONT][/COLOR][COLOR=#666600][FONT=inherit][[/FONT][/COLOR][COLOR=#000000][FONT=inherit]pathLength[/FONT][/COLOR][COLOR=#666600][FONT=inherit]])[/FONT][/COLOR][COLOR=#666600][FONT=inherit]),[[/FONT][/COLOR][COLOR=#000000][FONT=inherit]pathLengthChannel0[/FONT][/COLOR][COLOR=#666600][FONT=inherit]][/FONT][/COLOR][COLOR=#666600][FONT=inherit])[/FONT][/COLOR][/FONT]
[FONT=inherit][COLOR=#000088][FONT=inherit][/FONT][/COLOR][/FONT]
[COLOR=#666600][FONT=inherit]


#[/FONT][/COLOR][COLOR=#000000][FONT=inherit] pathLengthChannel[/FONT][/COLOR][COLOR=#666600][FONT=inherit]:=[/FONT][/COLOR][COLOR=#000000][FONT=inherit]SUMX[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR]
[COLOR=#000088][FONT=inherit]DISTINCT[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([[/FONT][/COLOR][COLOR=#000000][FONT=inherit]unqPath[/FONT][/COLOR][COLOR=#666600][FONT=inherit]]),[/FONT][/COLOR]
[COLOR=#000000][FONT=inherit]    SUM[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#008800][FONT=inherit]'query'[/FONT][/COLOR][COLOR=#666600][FONT=inherit][[/FONT][/COLOR][COLOR=#000000][FONT=inherit]pathLength[/FONT][/COLOR][COLOR=#666600][FONT=inherit]])[/FONT][/COLOR]
[COLOR=#666600][FONT=inherit])[/FONT][/COLOR]

Any ideas would be greatly appreciated. Many thanks.