Help needed on displaying closed cases while comparing todays data with yesterdays data extract

[rpar]

Hello everybody - Hope your having a great day !

Need your expert advise on the below scenario.

I have two table, Table 1 which is the new table extracted today being compared over table 2 extracted yesterday.

Both have the same set of primary key

Table1:

1 Orange

2 Orange

3 Orange

4 Orange

5 Orange

6 Banana

Table2 (yesterdays extract):

1 Orange

2 Orange

3 Orange

4 Orange

5 Orange

6 Banana

7 Orange

8 Orange

9 Banana

10  Banana

My questions is
When i create a matrix view for table1 it only shows the match count , but does not show the count that is not matched . In this case 7,8,9,10. It shows the table as below on KPI

Table 1

Orange 5

Banana 1

 Blank 4

I want to show these numbers against the Orange and Banace as they have been closed in table 1 without changing the count, it should be something like the below on KPI

Table 1

Orange 5 (Closed in previous 2)

Banana 1 (Closed in previous 2)

Any help would be great !

Thanks for your time and help !

[Anonymous · Accepted solution]

Hi @rpar ,

Please try to add a relationship.

Best Regards,

Wearsky

[rpar]

thanks this helped !

[Sahir_Maharaj]

Hello @rpar,

Can you please try this approach:

Closed_Display_Combined = 
VAR CurrentCategory = SELECTEDVALUE(Table1[Category])
VAR CurrentCount = 
    CALCULATE(
        COUNTROWS(Table1),
        Table1[Category] = CurrentCategory
    )
VAR ClosedCount = 
    CALCULATE(
        COUNTROWS(Table2),
        Table2[Category] = CurrentCategory,
        NOT Table2[PrimaryKey] IN VALUES(Table1[PrimaryKey])
    )
RETURN
IF(
    ClosedCount > 0,
    FORMAT(CurrentCount, "#") & " (Closed in previous " & FORMAT(ClosedCount, "#") & ")",
    FORMAT(CurrentCount, "#")
)

[rpar]

Thanks for taking time out @Sahir_Maharaj !
Tried the above query , it still shows the full count of table 1 and not the desired closed count of table 2 (4 in this example). Even if am able to get the output of 4 thats fine .