There's more...

While it may be interesting to know about the alternative form of the DISTINCT function, this recipe is perhaps a bit too easy. Never fear, this was really just preparatory to the more complex problem of finding distinct column combinations where the order does not matter. In other words, for instances such as ID 1, 9, and 10 where each row contains red, green, and blue, if we do not care about the order then all three of these rows should only be counted as 1 combination.

To implement this version of the recipe, do the following:

1. Create the following measure:

Combinations = 
    VAR __Table = 
        ADDCOLUMNS(
            SELECTCOLUMNS(
                'R09_Table',
                "__Key1",'R09_Table'[DimKey1],
                "__Key2",'R09_Table'[DimKey2],
                "__Key3",'R09_Table'[DimKey3]
            ),
            "__Max",
            MAX(
                MAX([__Key1],[__Key2]),
                [__Key3]
            ),
            "__Min",
            MIN(
                MIN([__Key1],[__Key2]),
                [__Key3])
        )
    VAR __Table2 = 
        SELECTCOLUMNS( 
            ADDCOLUMNS(
                __Table,
                "__Mid",
                SUBSTITUTE(
                    SUBSTITUTE(
                        [__Key1] & [__Key2] & [__Key3],
                        [__Max],"",1
                    ),
                    [__Min],"",1
                )
            ),
            "__Max",[__Max],
            "__Min",[__Min],
            "__Mid",[__Mid]
        )
RETURN COUNTROWS(DISTINCT(__Table2))

2. On the same Report page as before, create a third Card visualization and place the Combinations measure into the Fields area of the visualization.

Here, we start out similarly to our Permutations measure by creating a table variable, __Table, that uses SELECTCOLUMNS to return only columns that we care about from the R06_Table table. These columns are DimKey1, DimKey2, and DimKey3, and we rename these columns __Key1, __Key2, and __Key3 respectively. However, this time when calculating __Table we also use ADDCOLUMNS to add two columns, __Max and __Min. For the __Max column, we use an alternative version of the MAX function that accepts two DAX expressions as input as opposed to the normal single column reference. Since we desire the MAX of our three columns, __Key1, __Key2, and __Key3, we first find the MAX of __Key1 and __Key2 and then use this as the input to a second MAX function that evaluates the output of this expression along with __Key3. The calculation for our __Min column is identical to our __Max column except that we use the alternative form of the MIN function instead of the MAX function.

We now use our __Table variable as the basis for creating a second table variable, __Table2. Here, we use ADDCOLUMNS to add the __Mid column to our __Table variable. The calculation of our __Mid column uses two nested SUBSTITUTE functions. The first SUBSTITUTE function concatenates our __Key1, __Key2, and __Key3 columns and then substitutes the first occurrence of the value in our __Max column with nothing (""). We then use the output of this SUBSTITUTE function to feed our second SUBSTITUTE function, where we replace the first occurrence of the value in our __Min column with nothing (""). The value left from these two SUBSTITUTE statements is thus the third value from our __Key1, __Key2, and __Key3 columns. We now use SELECTCOLUMNS to select our three added columns, __Max, __Min, and __Mid.

Because of the calculations we have performed, we have effectively reordered rows with the same values in different columns into the same values in the same columns. Thus, we can now use DISTINCT coupled with COUNTROWS to return the count of the distinct rows within __Table2.