Count in two related tables with duplicated values

[knygneshys]

Hi guys,

i'm facing a little problem, maybe someone can help me. I have two tables related with ID. And I need to compare those two tables values and find missing ID. I can't create relationship between two tables, because those values not unique.  

In both tables ID's can be more or less values, in Excel it can be solved with COUNTIF and difference between values:

I need to sum only positive values.

I tried to go around with counting rows:

DIFFERENCE= COUNTA(TABLE 1[Dok Id])-COUNTA(TABLE 2[Dok Id]) 

And it's working only when i show each ID's in matrix table. But if i do not show ID's, values sum up with negatives values...

I hope I'll outline the problem clearly and anybody can help me with this problem...

[Floriankx]

Hello,

what would be your COUNTIF? Maybe we can translate it.

You could create CALCULATE(COUNTA(Table1[Dok ID]),FILTER(...)

[knygneshys]

Hi, i have tu tables with duplicates ID in both tables, so i created third table with unique ID and used formula:

counting_ID = CALCULATE(COUNTA(uniq[Dok Id]);FILTER(sutartys;COUNTA(sutartys[Dok Atask Id])))+0

i dont know why, but it's not counting properly...

so i'm using work around, just simple COUNT between to tables:

DIFFERENCE = COUNTA('procedūros'[Dok Id])-COUNTA(sutartys[Dok Atask Id]) +0

[v-yuta-msft]

Hi knygneshys,

This is because power bi will aggregate values in a column group by other columns in your visual automatically. Please click Modeling->Default Summarization-> Select "Don't summarize", then click data and check if the data is correct.

 

Regards,

Jimmy Tao

[knygneshys]

Maybe, you dont get the point... The point is, that i need summarize values of measure:

DIFFERENCE= COUNTA(TABLE 1[Dok Id])-COUNTA(TABLE 2[Dok Id]) 

But i need sum only off positive values, because i have and negative values... If i show in matrix by each ID, i can in Visual level filter set up "is greater than" "0", but if i show data in matrix by Other column, all values summarize, negative and positive, then i get not the same Total, if i sum only positive values...