Best approach for calculating counts

[Anonymous]

Hey All,

Looking for the best/correct approach to add a calculated count column to a table.

I have a clients table which is simple at first sight.

Status

Type

Sex

Count

The count column is a calculated one. It is also not complicated.

CALCULATE(DISTINCTCOUNT(CLIENTS[ID]),
FILTER(....),
FILTER(....),
FILTER(....),
FILTER(....))

Each row count differs from one another by sex ,age range, and status. Sex and status are availble in the table, while age is from a different table.

I easily created a few measures, with the other table, which represents each record in the table.

How do I connect it all together?

Cheers!
A

[v-xicai]

Hi @Anonymous ,

Based on having created relationships among the tables. you can try to create measure like DAX below, multiple FILTER function can be used in a formula, maybe the filter condition about [age] part need to be changed by the actual request.

Count = CALCULATE(DISTINCTCOUNT(CLIENTS[ID]),FILTER(ALLSELECTED(CLIENTS),CLIENTS[Status]=MAX(CLIENTS[Status])&&CLIENTS[Sex]=MAX(CLIENTS[Sex])),FILTER(ALLSELECTED(Other_table),Other_table[age]=MAX(Other_table[age])))

If I misunderstand it, could you please share your sample data or desired output screenshots for further analysis, you can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-xicai 

Thanks for the answer.

the issue is that the sex/status/age changes for every record.

The table I have is a standalone and cannot be related to any other table. i.e. no foreign key to other table(s).

The current table I have is (For example)

_STATUS	CHECK_TYPE	SEX	ELIGIBLE_CLIENTS (Current)	CLIENT_GRP_NO	ELIGIBLE_CLIENTS (Expected)
Current	Over 15	F	2499	1	1224
Past	Over 15	F	1432	5	562
Transient	Over 15	F	159	2	77
Current	Over 15	M	2709	1	1416
Past	Over 15	M	1511	5	73
Transient	Over 15	M	140	2	650
Current	Over 55	F	2499	1
Past	Over 55	F	1432	5
Transient	Over 55	F	159	2
Current	Over 55	M	2709	1
Past	Over 55	M	1511	5
Transient	Over 55	M	140	2
Current	Over 75		3	1
Past	Over 75		6	5
Transient	Over 75		4	2
Current	Over 75	F	3175	1
Past	Over 75	F	2905	5
Transient	Over 75	F	563	2
Current	Over 75	M	3318	1
Past	Over 75	M	2876	5
Transient	Over 75	M	354	2

The current ELIGIBLE_CLIENTS number is showing a wrong number, as it is not filtered correctly.

I have a measure outside the table that shows the correct one. For instance

Eligible_Clients = CALCULATE(DISTINCTCOUNT(CLIENTS[ID]),
FILTER(...),
FILTER(...),
FILTER(...),
FILTER(...)
)

Can I use these measures in a table? If yes, how?
Or, can I apply filters on specific values in a table?

Thanks
A

[v-xicai]

Hi @Anonymous ,

Confused about what is your desired output now, for example the relationship between the main table and other table.

If you need to create relationship between two tables which have no foreign key, you can create intermediate table like DAX below, then create relationships between this intermediate table and other two tables.

intermediate table = CROSSJOIN(DISTINCT(SELECTCOLUMNS(Table1,"name1",Table1[field1])),DISTINCT(SELECTCOLUMNS(Table2,"name2",Table2[field2])))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-xicai 

TYPO.

That shold be clients.

The question is really, how to use/relate a measure in a table.

ThankS

A

[MarkPalmberg]

@v-xicai , I just wanted to leave you a note to say that your comment about the DISTINCTCOUNT calculation *really* helped me overcome a hurdle I had regarding counts across related tables in a (sort of) star schema. Thanks so much for contributing this.

Will this calculation travel across tables? I.e., if I have a table of distinct values between two tables, do I need to specify USERELATIONSHIP, or can I just name the table and column regardless of how "far away" it is from the table where I'm generating the count? I hope that makes some sense....