Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Single Cross Table Visualization for Multiple Data Tables

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Thank you amitchandak 

Hi, Anonymous 

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.