04-07-2018 13:34 PM - last edited 06-27-2018 10:32 AM
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
For more than 3 values, would you suggest a strategy of concatination, PATH, and PATHLENGTH, instead of MIN and MAX?