Trying to compare to a subquery doesn't work

[nielsvanleusden]

Hi Guys, first post overhere so excuse me for any mistakes in posting it.

I'm trying to count rows for a custom measure in one of my reports.

It should calculate two filters, one is very simple the second has been breaking my head.

In SQL it works fine and looks like this:
SELECT COUNT("id")
FROM "table1"
WHERE "status" = 'x' AND "user" NOT IN (SELECT "userId" FROM table2);

It's the bold part that cracks my brain..

How do i get the same result in DAX?

I've already got the part of: Measure = CALCULATE(COUNT('table[id]), '[status] = "x")

Thanks in advance..

[Zubair_Muhammad]

Hi @nielsvanleusden

I am not sure...But give it a shot

Could you show some data and expected results?

Measure =
CALCULATE (
    COUNT ( Table1[id] ),
    Table1[status] = "x",
    NOT ( CONTAINS ( Table2, Table2[userid], SELECTEDVALUE ( Table1[UserId] ) ) )
)

[nielsvanleusden]

@Zubair_Muhammad 

I think that could work, turns out i've been missing some functions because of the DirectQuery connection.

Will try it again a bit later on.

Thanks for your reply!