Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Distinct count based on the another table column

I have two tables 'Table 1' and 'Table 2' both tables have column names ID.NR, Date, etc. these two tables are joined by IDNR, neither column contains unique values for column IDNR so the relation type is many to many and the cross filter direction is both.
I want to count distinct values of IDNR for Table 1, for the year 2018 if IDNR also exists in table 2. I tried to create a calculated column using the following formula but it didn't give me the right results.

Kolumn = CALCULATE(DISTINCTCOUNT(Table 1[IDNR]),FILTER(ALL(table 2)
,Table 2 [IDNR] = Table 1[IDNR] && Table 2[Year] = Table 1[Year]))

3 REPLIES 3
az38
Community Champion
Community Champion

Hi @Anonymous 

try something like that

= countrows(
distinct(
INTERSECT(
SELECTCOLUMNS(FILTER(ALL('Table 1');YEAR('Table 1'[Date]) =2018);"IDNR";[IDNR]);
SELECTCOLUMNS(FILTER(ALL('Table 2');YEAR('Table 2'[Date]) =2018);"IDNR";[IDNR])
)
)
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

@az38 Thank you for your reply!
I want to visualize distinct count of IDNR by other columns named 'Age group' and 'sex', and these columns exist only in Table 1, so the measure gives me the same value for all age group and sex, and I think its because these columns 'Age group' and 'sex' are not included in the selected columns. Is there another way to solve this?
Thanks!

az38
Community Champion
Community Champion

@Anonymous 

not sure I understand you correct, but if so you could execute the next workaround

1. create a table

IntersectTable= 
distinct(
INTERSECT(
SELECTCOLUMNS(FILTER(ALL('Table 1');YEAR('Table 1'[Date]) =2018);"IDNR";[IDNR]);
SELECTCOLUMNS(FILTER(ALL('Table 2');YEAR('Table 2'[Date]) =2018);"IDNR";[IDNR])
)
)

this table will contain all distnct IDNR which exists in 2018 year

2. create relationships between your table 1 table with 'Age group' and 'sex' columns by field IDNR.

3. Visualize it as you wish. for example create table visual with columns:

'IntersectTable'[IDNR]

'Table1'[Age group]

'Table1'[Sex]

 

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors