topic Re: Using count distinct with set analysis in App Development

Using count distinct with set analysis

DanielleW1 — Wed, 16 Jul 2025 14:02:29 GMT

I have a query around using set analysis with count distinct and have read that this can sometimes not be fully trusted. This happened in my app where the previous year count matched the current year which isn't correct.

What I am hoping to do is calculate the number of distinct transactions but also look at the previous year. This can have outside factors with filtering which I would like to be applicable to the previous year calculation also.

Our transactional data contains multiple lines per transaction to show the line level detail of products, and these also have departments within them that need to apply to set analysis - so I was unable to use a dummy field of '1' for example.

Any help on this would be greatly appreciated, thanks in advance.

Re: Using count distinct with set analysis

Or — Wed, 16 Jul 2025 14:13:07 GMT

You will have to be more specific (ideally with sample data and an expected result), I think. This simply isn't enough concrete information to try and help.

Re: Using count distinct with set analysis

DanielleW1 — Wed, 16 Jul 2025 14:29:31 GMT

I currently use the following expressions:

COUNT(DISTINCT(({$<Year={'$(vSelectedYear)'},[Transaction Type]={'RE','SA'}>}[Transaction ID])))

COUNT(DISTINCT(({$<Year={'$(vPrevYear)'},[Transaction Type]={'RE','SA'}>}[Transaction ID])))

Where vSelectedYear = 2025 and vPrevYear = 2024.

This works as expected with the year changes when using a SUM of Sales for example:

SUM(({$<Year={'$(vPrevYear)'},[Transaction Type]={'RE','SA'}>}[Sales Value]))

However both of the COUNT DISTINCT expressions give me the same result.

Re: Using count distinct with set analysis

Or — Wed, 16 Jul 2025 16:28:23 GMT

Looks to be running fine with some sample data I whipped up:

Load * INLINE [
Year, Type, TransactionID
2024, A, 1
2024, A, 2
2024, B, 1
2024, B, 3
2024, C, 3
2025, A, 3
2025, B, 3
2025, B, 4
2025, C, 5
];

Re: Using count distinct with set analysis

rwunderlich — Mon, 21 Jul 2025 04:12:30 GMT

If [Transaction ID] is a key field, yes it's possible to get some unexpected results depending on how your data model is structured. Recommended to count a non-key field instead.

-Rob