Power BI DAX: Count Distinct measure with row pair filter context

Question

sitting on this for longer right now, this is my data:

+------------+---------+------------+
| OrderID    | Status  | Text       |
+------------+---------+------------+
| 1          | rel     | W3-A       |
+------------+---------+------------+
| 1          | conf    | log        |
+------------+---------+------------+
| 3          | rel     | W3-A       |
+------------+---------+------------+
| 4          | rel     | W3-B       |
+------------+---------+------------+
| 5          | rel     | W3-C       |
+------------+---------+------------+
| 6          | rel     | W3-B       |
+------------+---------+------------+
| 6          | conf    | log        |
+------------+---------+------------+
| 7          | conf    | log        |
+------------+---------+------------+
| 8          | rel     | W3-B       |
+------------+---------+------------+
| 8          | rel     | log        |

Now I would like to have a measure which shows:

=Count every distinct orderID which has (Status=rel && text= starting with "W3") but has also a row with (Status=conf && text=log)

That would result in a total number of "2": OrderID 1 and OrderID6 fulfilling these conditions

As table it would look like this:

+------------+---------+------------+------------+
| OrderID    | Status  | Text       | distinctCount
+------------+---------+------------+------------+
| 1          | rel     | W3-A       |   1
+------------+---------+------------+------------+
| 1          | conf    | log        |   1
+------------+---------+------------+------------+
| 3          | rel     | W3-A       |
+------------+---------+------------+------------+
| 4          | rel     | W3-B       |
+------------+---------+------------+------------+
| 5          | rel     | W3-C       |
+------------+---------+------------+------------+
| 6          | rel     | W3-B       |   1
+------------+---------+------------+------------+
| 6          | conf    | log        |   1
+------------+---------+------------+------------+
| 7          | conf    | log        |
+------------+---------+------------+------------+
| 8          | rel     | W3-B       |
+------------+---------+------------+------------+
| 8          | rel     | log        |
-------------+---------+------------+------------+
TOTAL                                    2

So Dragging the measure in a "Card" visualisation should simply show

 Order CountDist
    +--------------+
           2
    +--------------+

user_765226 · Answer

You can find the IDs that satisfy each set of conditions and then take the intersection to find which of them satisfy both.

DistinctCountPairs =
VAR rel_W3 =
    CALCULATETABLE (
        DISTINCT ( Orders[OrderID] ),
        Orders[Status] = "rel",
        LEFT ( Orders[Text], 2 ) = "W3"
    )
VAR conf_log =
    CALCULATETABLE (
        DISTINCT ( Orders[OrderID] ),
        Orders[Status] = "conf",
        Orders[Text] = "log"
    )
RETURN
    COUNTROWS ( INTERSECT ( rel_W3, conf_log ) )

If you want, you can switch the final line to

CONCATENATEX ( INTERSECT ( rel_W3, conf_log ), Orders[OrderID], "," )

to print a list of which IDs are in the intersection.

Power BI DAX: Count Distinct measure with row pair filter context

1 Answers