Forum Discussion
Distinct count based on the another table column
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
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!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