Using count distinct with set analysis

[DanielleW1]

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.

[Or]

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. 

[DanielleW1]

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.

[Or · Accepted solution]

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
];

[rwunderlich]

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