Single Cross Table Visualization for Multiple Data Tables

[Shri_1004]

Hi All,

I am new to Power BI and I need some help. 

1. I have to compare the data between two tables having same number of columns and data types. This two tables are for single entity only. For example, Entity A has Table1 and Table2. This comparison will be based on specific column - 'Status', for mismatch data, count needs to be calculated

Sample Data below : for ID 2 and 4, Status is not matching so this count needs to be calculated. 

Table 1
ID	Number	Status
1	120	New
2	122	Close
3	125	Open
4	127	Close

Table 2
ID	Number	Status
1	120	New
2	122	Open
3	125	Open
4	127	Open

2. for the same tables, we have to check for the missing data for Number and calculate the count for both Table 1 and Table 2. I have tried with Number = Null filter on both tables 

Table 1 : Count : 2

ID	Number	Status
1	120	New
2	122	Close
		Open
		Close

Table 2 : Count is 1 

Table 2
ID	Number	Status
1	120	New
3	125	Open
4	127	Open

 
for all these calculation I have to display this in a cross table like below for all entities A,b,c,.... having Table 1 and Table 2

Table Name	Status Mismatch Count	Table 1 Missing Data	Table 2 Missing Data
A	2	2	1
B
C

Can somebody please help me ? 

@amitchandak ur inputs will be appreciated!
Thanks in advance! 

[v-jianpeng-msft]

Thank you amitchandak 

Hi, @Shri_1004 

Based on your description, I used the data you provided:

I've created some measures using the following DAX expressions:

Not matching = 
VAR _table1= SUMMARIZE('Table1','Table1'[ID],'Table1'[Status])
VAR _table2= SUMMARIZE('Table2','Table2'[ID],'Table2'[Status])
VAR _cross_table = FILTER(CROSSJOIN(_table1,_table2),'Table1'[ID]='Table2'[ID])
RETURN COUNTAX(FILTER(_cross_table,'Table1'[Status]<>'Table2'[Status]),'Table1'[ID])

Missing Data CountTable 3 = 
VAR _table3 = SUMMARIZE('Table3','Table3'[ID],'Table3'[Number ],Table3[Status])
VAR _table4= SUMMARIZE('Table4','Table4'[ID],'Table4'[Number ],'Table4'[Status])
VAR _avaliable_In_table3 = SELECTCOLUMNS(FILTER(CROSSJOIN(_table3,_table4),'Table3'[ID]='Table4'[ID]),'Table3'[ID])
RETURN COUNTAX(FILTER(_table4,NOT 'Table4'[ID] IN _avaliable_In_table3),'Table4'[ID])

Missing Data CountTable 4 = 
VAR _table3 = SUMMARIZE('Table3','Table3'[ID],'Table3'[Number ],Table3[Status])
VAR _table4= SUMMARIZE('Table4','Table4'[ID],'Table4'[Number ],'Table4'[Status])
VAR _avaliable_In_table3 = SELECTCOLUMNS(FILTER(CROSSJOIN(_table3,_table4),'Table3'[ID]='Table4'[ID]),'Table3'[ID])
RETURN COUNTAX(FILTER(_table3,NOT 'Table3'[ID] IN _avaliable_In_table3),'Table3'[ID])

Here are the results:

With the above metrics, you'll be able to count the status mismatches or missing data in your table. 

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Shri_1004 , Create a common ID Table

ID = distinct(union(distinct(Table1[ID]),distinct(Table2[ID]) ))

Join with both tables

Now have measure like

n1 = sum(Table1[Number])

n2= sum(Table2[Number])

S1 = sum(Table1[Status])

n2= sum(Table2[Status])

Now you can have measures like

Count Matched =

Countx(Values(ID[ID]) , if([n1] = [n2], [ID], blank()) )

Count not Matched =

Countx(Values(ID[ID]) , if([n1] <>  [n2], [ID], blank()) )

Status Matched =

Countx(Values(ID[ID]) , if([s1] = [s2], [ID], blank()) )

Status not Matched =

Countx(Values(ID[ID]) , if([s1] <>  [s2], [ID], blank()) )

[Shri_1004]

Hi @amitchandak Thank you so much for your inputs!!
For Point 1 only we have to do the comparision between Table1 and Table2 based on Status column. 

For Point 2, we have to find the count of missing data.

for example, data which is available in Table 1 but not in Table 2 ---- Then this missing data count for Table 2 need to find and display in cross table. Same goes for data which is available in Table 2 but not in Table 1--- this will be missing data for Table 1.

This data can be calculated as you have explained but still I have a concern on how to display this data in cross table. Because I have a 100+ entities like A,B,C.... etc having 2 tables. So i have to display it in below format.

Table Name	Status Mismatch Count	Table 1 Missing Data Count	Table 2 Missing Data Count
A	2	2	1
B
C