I want to Display the Number of Cases that exist in the Cases Table but not in another Table.
For example with the data shown in the picture I want to show 2 since Cases 4 and 5 exist in the Cases Table but not in Table 2.
Currently my problem is that when I count the total entries of both tables in separate single KPI fields they show the Correct Values:
COUNT (DISTINCT (“CASES”.”ID”) = 5 and COUNT (DISTINCT (“TABLE_2“.”ID”)) = 3
But as soon as I combine both formulas to get the difference as:
COUNT (DISTINCT (“CASES”.”ID”) - COUNT (DISTINCT (“TABLE “.”ID”))
It returns 0 which of course isn’t correct as I would expect to get 2 (5 - 3).
After some trying I noticed that when I add both values it returns 6 so it seems that the value for the total amount of cases changes from 5 to 3 which also explains the 0 it returns when subtracting.
In the Data Modell Cases.ID is connected with Table_2.ID
I have also tried solving it with a Case When Statement.
COUNT (DISTINCT (CASE WHEN “CASES”.”ID” = “Table_2”.”ID”
But it also returns 0.
I have tried the solutions from the following posts but all with no luck…
Thanks in advance