Distinct Column Combinations 04-07-2018 13:34 PM - last edited 06-27-2018 10:32 AM

[Greg_Deckler]

Suppose you have some data like this:

ID            DimKey3            DimKey4          DimKey5          DimKey6

1	blue	red	green	green
2	blue	blue	red	blue
3	blue	red	blue	blue
4	green	green	red	green
5	green	green	blue	green
6	red	red	red	red
7	blue	blue	blue	blue
8	green	green	green	green
9	blue	green	red	green
10	blue	red	green	green

You wish to find how many unique combinations that you have for just DimKey3, DimKey4 and DimKey5. DimKey6 does not matter.

To get the number of distinct rows where order matters:

mDistinctCombos = 
VAR tmptable1 = SELECTCOLUMNS(DistinctCombos,"key3",[DimKey3],"key4",[DimKey4],"key5",[DimKey5])
VAR tmptable2 = DISTINCT(tmptable1)
VAR distcount = COUNTROWS(tmptable2)
RETURN distcount

To get the number of distinct rows where order does not matter:

mDistinctCombosUnique = 
// This measure gets unique keys where order doesn't matter. In other words, red, green, blue is the same as blue, red, green
// Start by getting only the columns we care about
VAR tmptable1 = SELECTCOLUMNS(DistinctCombos,"key3",[DimKey3],"key4",[DimKey4],"key5",[DimKey5])
// Add columns that return the MAX and MIN of all 3 columns for each row
VAR tmptable1a = ADDCOLUMNS(tmptable1,"max",MAX(MAX([key3],[key4]),[key5]),"min",MIN(MIN([key3],[key4]),[key5]))
// Create a single string with all 3 column values and remove the max and min values calculated above, we now have our 3rd value
VAR tmptable1b = ADDCOLUMNS(tmptable1a,"mid",SUBSTITUTE(SUBSTITUTE([key3] & [key4] & [key5],[max],"",1),[min],"",1))
// We have now effectively "sorted" the values in the three columns for each row, so select our new 3 "sorted" columns
VAR tmptable2 = SELECTCOLUMNS(tmptable1b,"max",[max],"min",[min],"mid",[mid])
// Now get the distinct rows in the table
VAR tmptable3 = DISTINCT(tmptable2)
// Count the number of rows
VAR distcount = COUNTROWS(tmptable3)
RETURN distcount

eyJrIjoiNzdmMDAwYTgtMGE3MS00ZTk4LTg1ZTctN2M0Y2RlMmZjNTc3IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[EnglishCJ]

For more than 3 values, would you suggest a strategy of concatination, PATH, and PATHLENGTH, instead of MIN and MAX?