Single distinct count measure on a single table based on two filters - each filter value on distinct rows of the single table

Question

I'm trying to demo some more complex DAX / powerpivot concepts to people on my team. I came up with an example to walk them through, but I think might actually be impossible.

Happy to change the example to something else, but this has really piqued my interest. Does anyone know how to do it?

Here's some example data, single table, nothing fancy...

    Child ID | Type      | Parent ID
    1        | A         | 1
    1        | A         | 2
    1        | B         | 3
    1        | C         | 4
    2        | B         | 436
    2        | B         | 983
    2        | A         | 24
    2        | A         | 444
    3        | A         | 67
    3        | A         | 67
    3        | B         | 4
    3        | B         | 3

What I'd like to do is - per Child ID, Distinct Count Parent IDs of Type A which have a Parent ID of 4 attributed to it somewhere... in a single measure...

So the result in a pivot would be....

    Child ID | DCnt_PiD
    1        | 2
    2        | 0
    3        | 1

I have played around with this in a DAX query editor, trying crossjoins, generates mixed with addcolumns etc. but they didn't work (cannot join a table with the same column id etc.)...

The measure would then relate over to another table via a bridge table, so can't use a pivot I'm afraid...

Anyone got any ideas?

Cheers

dijksterhuis dijksterhuis · Accepted Answer · 2016-04-13T16:06:25

Cracked it in a DAX Query, now time to turn it into a measure!

EVALUATE
 (
    SUMMARIZE (
        FILTER (
            SUMMARIZE (
                table,
                table[Child_ID],
                table[Type],
                table[Parent_ID]
            ),
            table[Type] <> "B"
                && table[Type] <> "C"
                && CALCULATE (
                    COUNTROWS ( table ),
                    ALLEXCEPT ( table, table[Parent_ID], table[Child_ID] ),
                    table[Parent_ID] = 4
                )
                    > 0
        ),
        table[Child_ID],
        "DCnt_Pid", DISTINCTCOUNT ( table[Parent_ID] )
    )
)
ORDER BY [Child_ID]

Single distinct count measure on a single table based on two filters - each filter value on distinct rows of the single table

1 Answers