Forum Discussion
Help needed on displaying closed cases while comparing todays data with yesterdays data extract
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 !
4 Replies
- Sahir_Maharaj
Super User
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, "#") )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 .
thanks this helped !
