cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## Count in two related tables with duplicated values

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...

4 REPLIES 4
Solution Sage

Hello,

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

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

Frequent Visitor

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

Community Support

Hi

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

Frequent Visitor

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...

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors